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

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

Answer 3

Hi,

Rather than all those unqualified uses of ROW and COLUMN, plus the volatile INDIRECT/ADDRESS combinations, you'd be much better off using e.g. INDEX, if possible.

What are the cells which you wish to be considered? It appears to depend on the value of a cell (or cells) in a certain position with respect to the active cell - can you clarify?

Regards

Answer 4

Great - thanks Hans for the help especially the OR(B3:B6)="") rather than having to type out multiple statements.

I was trying to avoid using specific cell addresses (even though they update whe you copy / move). Have you any idea why the option with ROW(),COLUMN() didn't work?

Answer 5

Select the cells you want to apply conditional formatting to; it doesn't have to be a contiguous range.

Look at the address box on the left hand side of the formula bar and make note of the active cell within the selection.

In the following, I'll assume that B2 is the active cell.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=OR(B3:B6="")

B3:B6 are the four cells immediately below B2. If the active cell is not B2, adjust the formula accordingly.

Please note that the range reference B3:B6 is relative, not absolute as in $B$3:$B$6. This is essential!

Click Format... and specify the desired formatting.

Click OK, then OK again.

You can also apply conditional formatting to one cell, then use the Format Painter to copy/paste the formatting, including conditional formatting, to other cells.