Excel Conditional Formatting Self-Management

Question

I am trying to do conditional formatting in Excel 2003, I want the cell to turn red if the next cell is “Yes” and highlighted in bold if it itself contains “Yes”. Therefore, 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 did not use a direct link to the cell, but I got the following.

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

But that always fails. However, if I use by itself ( INDIRECT(ADDRESS(ROW(),COLUMN()))="Yes" to make the current sale bold), it works.

Does anyone know how I can refer to a cell on my own in Excel 2003 conditional formatting?

Answer 1

I do not have Excel 2003 for testing, only 2007. However, I think you can solve your problem with the following rules. Suppose your first column is A and the second is B with the first data value in A1.

The first rule in A1 is highlighted using the formula =and(A1="Yes",B1="Yes") , and the format is in bold and a red background.

The second rule in A1 is highlighted using the formula =B1="Yes" , and the format is only a red background.

The third rule in A1 is highlighted when the cell is “Yes” and the format is in bold.

Then copy this format to all the other cells in column A using the layout layout. Excel will automatically set up cell links for you, so in A2 the formula applies to B2, in A3 it refers to B3, etc. Note that the order of the rules is important because Excel 2003 will stop after the first matching rule. This is why you cannot get the behavior you want with only two rules.

You can also copy the same format to column B, assuming what you want. Otherwise, format B1 with the rule so that it is bold when the cell is “Yes” and copy it to the rest of column B.