Using ROW() and COLUMN() in a conditional formatting formula

Question

Hi All,

I need to apply conditionl formatting to a cell (B2) depending on the values of the cells below. I have..

=OR($B$3="",$B$4="",$B$5="",$B$6="")

which works. All OK so far.

However ... need to apply this in several places in my spreadsheet and so I need to make it more generis (less specific) so I have ..

=OR(INDIRECT(ADDRESS(ROW()+1,COLUMN()))="",INDIRECT(ADDRESS(ROW()+2,COLUMN()))="",INDIRECT(ADDRESS(ROW()+3,COLUMN()))="",INDIRECT(ADDRESS(ROW()+4,COLUMN()))="")

Which doesn't work. Not OK :-(

If I replace the ROW() and COLUMN() statements with the row and column numbers, the formatting works as expected.

So where am I going wrong???

TIA

Mark

Answer 1

Hi,

Similar to what you were trying to do in the formula you posted earlier, you could use this conditional format formula. Put it in any cell and it will test for 4 x's in the 4 cells below it and if there are <4 it will evaluate as TRUE.

=COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,0,4,1),"X")<4

I have no idea why your first formula didn't work, it evaluates perfectly in a cell on the worksheet but as you note fails as a conditional format formula.

Answer 2

It's not, no. I want to remove all absolute references to Columns (A,B,C...) and Rows (1,2,3...) and replace them with functions that evaluate the position of the current cell.

Mark

Then simply remove all "dollar-signs" from the formula I posted.

With all references relative, and providing you are careful when setting it up in CF, that formula will always return TRUE if at least one of the four cells below the cell in question is blank.

I would recommend still making the Applies To range absolute though, even if you make it a very large area of the spreadsheet, just to avoid potential issues.

Regards

Answer 3

It's not, no. I want to remove all absolute references to Columns (A,B,C...) and Rows (1,2,3...) and replace them with functions that evaluate the position of the current cell.

Mark

Answer 4

XOR,

 I have a range of cells B2:E2 each of which contains a keyword, underneath this I have a range of cells B3:E6 each of which contains either X or "" depending on whether a specific sheet in the workbook contains the keyword  ...

In this example there are 4 columns but this is not always the case. As you can see Worsheet 3 does not contain the keyword Added - so that cell is hilighted.

Mark

Ok, so with B2:E2 highlighted in the sheet (and B2 the active cell), define a custom, formula-based Conditional Formatting with this formula:

=COUNTIF(B$3:B$6,"")>0

Is that what you were after?

Regards

Answer 5

I don't know why the formula with INDIRECT doesn't work, but there are more reports of conditional formatting failing with INDIRECT in the formula. So it's best to avoid it.