Conditional formatting of a column of cells

Richard - Jun 3, 2010 at 08:46 AM
 Trowa - Jul 16, 2010 at 09:39 AM

I am working on a task list/project sheet and I am trying to set a conditional format for a column of cells that is based on two (technically three I guess) conditions to highlight the expected due date if:

1) The date in the cell is prior to Today (all values are dates) highlight red and change text to dark red

2) AND The value in the adjacent cell does not equal "Complete" turn the text bold

3) If the cell is BLANK apply no formatting (meaning I haven't added a task to that row yet)

I can get the color to change using a conditional formating rule on that column "Formula is < TODAY()"

but I can't figure out how to get the relative value to bold the text based on whether the value of the cell next to it is NOT the word "Complete". For some reason it's using the absolute position or value of the first cell in the column.

I also can't figure out how to keep it from formatting if blank.

To help - the expected due date values are in G2:G200 and their corresponding completion status are in H2:H200.

I am using Excel 2007, latest patch.

Thanks in advance your expert guidance!

2 responses

Hi Richard,

Try this:
1) =IF(G2<TODAY(),IF(H2<>"Completed",TRUE,FALSE),FALSE)
Format: red cell, red bolt text.
2) =G2<TODAY()
Format: red cell, red text.

For your third condition you can't use G2 as a reference, cause when G2 is blank it's also earlier then today. So look for another cell which is also filled and emptied the same as G2, let's say F2.
3) =F2=""

Best regards,
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 3, 2010 at 10:56 AM
Trowa, you can have the third condition embedded in two conditions, that is apply condition if the cell is not blank

=AND(G2<>"", H2<>"", G2<TODAY(), H2<>"Completed")
The above says that if G2 is not blank and H2 is not blank and G2<today and H2<> completed

and second
=AND(G2<>"", H2<>"", G2<TODAY())
The above says if G2 and H2 is not blank and G2 < today
Didn't see this post untill now.
Just want to thank Rizvisa for providing a cleaner solution.
Thanks All, both solutions filled my needs, rizvisa1's was a little more elegant.