Excel Conditional Formatting Self Reference

Question

I'm trying to do some conditional formatting in Excel 2003, what I want to do is have a cell go red if the next cell is "Yes" and go bold if it, itself, contains "Yes". So if both cells are "Yes" the current cell will be red and bold. I want to do this for a group of cells so I haven't used a direct reference to the cell, but I have come up with the following.

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))="Yes",INDIRECT(ADDRESS(ROW(),COLUMN()+1))="Yes")

But this always fails. However, if I use either on its own (INDIRECT(ADDRESS(ROW(),COLUMN()))="Yes" to make the current sell go bold), it works.

Does anyone know how I can self reference a cell generically in excel 2003 conditional formatting?

Answer 1

I don't have Excel 2003 to test on, only 2007. However, I think you can solve your problem with the following rules. Assume that your first column is A and the second is B with the first data value in A1.

First rule on A1 is highlight using a formula of =and(A1="Yes",B1="Yes") and the format is bold text and red background.

Second rule on A1 is highlight using a formula of =B1="Yes" and the format is red background only.

Third rule on A1 is highlight when cell equals "Yes" and the format is bold text only.

Then copy this format to all the other cells in column A with the format painter. Excel will automatically adjust the cell references for you so that in A2 the formula refers to B2, in A3 it will refer to B3, etc. Note that the order of the rules is important because Excel 2003 will stop after the first rule that matches. That's why you can't get the behaviour you want with only two rules.

You can also copy the same format to column B, assuming that's what you want. Otherwise, format B1 with the rule to bold when cell equals "Yes" and copy that to the rest of column B.