Excel - Self reference a cell in conditional formatting

1
votes

I am trying to create a conditional format formula to colour a cell based on the value of itself, the cell next to it on the right, and whether the cell on the first row in the same column is a weekday or not.

Currently it works correctly as follows:

=AND($A$2=0,$B$2=0,WEEKDAY($A$1)<>1,WEEKDAY($A$1)<>7)

I have a lot of cells I'd like to use this formula in, so I thought about creating a cover-all formula that used the ADDRESS function.

My original idea was to use:

=AND((ADDRESS(ROW(), COLUMN()))=0,$B$2=0,WEEKDAY($A$1)<>1,WEEKDAY($A$1)<>7)

(I've changed just one cell reference here for example, but I'd like to change all 4 if possible).

However, when I try to use ADDRESS(ROW(), COLUMN()) in place of an absolute cell reference, the formula doesn't format the cell anymore.

Is there a way to make this cover-all formula work so that I don't have to go through and change the referenced cell values each time for every cell? Am I missing something about the syntax? Or will this simply not work the way I'd like it to? Thanks in advance!

1
excelexcel-formula

1 Answers

0
votes

ADDRESS returns a string that looks like a cell address and "$A$2"<>0.Wrap the ADDRESS in INDIRECT or rewrite your original without absolute rows.

=AND(indirect(ADDRESS(ROW(), COLUMN()))=0, indirect(ADDRESS(ROW(), COLUMN()+1))=0, WEEKDAY($A$1)<>1, WEEKDAY($A$1)<>7)
=AND($A2=0, $B2=0, WEEKDAY($A$1, 2)<6)