Help with Conditional Formating and formula with multiple conditions

[Snoddas]

Hi all!
I have a schedule in Excel 2007 with dates and also a checkbox for each entry so it can be checked when completed. I have created a Conditional Format that formats the cells for each entry when the check box is ticked. I did this by connecting the checkbox with the cell the box is located in so it enters the value TRUE in that cell and triggers the formatting.

But, now i am trying to use the same method to get the cells of the entry to be formated if the entry fails to be completed on it's set date. For this to work as desired i need to have a formula entered into Conditional Formatting that triggers when the date has passed and the checkbox connected cell is still empty. I have been thinking of a combination of IF and ISBLANK but i just can't get it to work.

See attached example file for how the TRUE Conditional Format is working now.

Hope someone can help

[benishiryo]

hi Snoddas, welcome to the forum. i noticed you have done your Conditional Formatting one by one. delete all of your existing rules & here's how you can do it. select the range you want to apply to (say from A3:C10)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=$C3
that is for your existing green

to highlight those that fails to be completed, do the same steps again, but use this formula:
=AND($A3<TODAY(),$C3=FALSE,$A3<>"")

[Snoddas]

Hi Ben,
Well i am thinking of using seperate Conditionals because i just want the row where i tick the checkbox to get marked.

Thank you for that formula, but when i pasted it as is into the Format field of Conditional Formatting, Excel says that there is an error in the formula. Might you have missed something?

[FDibbins]

I tried Bens formula in CF and it did not give an error message - but it also did not change any colors

This will highlight all rows that have a check-mark...

1. highlight the range you want to apply the conditional formatting to (A3:C4)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$C3=TRUE format fill as required

[benishiryo]

when encountering an error with our formulas, 1 possibility is that your region settings is using semi-colons instead of commas. you can try changing our formulas' commas to semi-colons.

I tried Bens formula in CF and it did not give an error message - but it also did not change any colors

attached file for what i think OP wants

[Snoddas]

when encountering an error with our formulas, 1 possibility is that your region settings is using semi-colons instead of commas. you can try changing our formulas' commas to semi-colons.

Brilliant! Why didn't i think of that LOL The change to Semi-colon did the trick Ben, formula works like a charm!

Thank you so much for all your help.