Excel Conditional Formatting Self Reference

excel worksheet-function conditional-formatting
Henry B picture Henry B · Nov 28, 2008 · Viewed 10.5k times · Source

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

Jeremy Bourque picture Jeremy Bourque · Nov 28, 2008

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.

Related questions

Change background color of the row or range if a particular cell value is equal to some text

What I have: 1 Col A Col B Col C Col D Col E Col F 2 3 Approved Text Text Text Text Text 4 Reject Text Text Text Text Text 5 Pending Text Text Text Text Text What I want is : If Column A …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More