Highlight Row depending on two separate cells

[Miguelluis]

Hi

I'm stuck with this one, the below is an example of what I'm trying to achieve. I need to highlight the rows depending on the values on D and F. I've done =NOT(ISBLANK($P1)) in conditional formatting to highlight the rows when a date is entered in column D but I can't figure out how to override this if 'Yes' is entered into F. In short I want to shade grey if there's a date in D but shade yellow if yes is populated in F. If no is entered in F it stays grey.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]12/09/14[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]01/09/14[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]05/08/14[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Miguel

 

[Momentman]

Maybe use two rules for the conditional formatting

Highlight cells A2 to F4(or the last row)
1. =AND(NOT(ISBLANK($D2)),$F2<>"Yes") - Format as Grey
2. =$F2="Yes" - Format as yellow

 

[Miguelluis]

Maybe use two rules for the conditional formatting

Highlight cells A2 to F4(or the last row)
1. =AND(NOT(ISBLANK($D2)),$F2<>"Yes") - Format as Grey
2. =$F2="Yes" - Format as yellow

Hi

Thanks I'm so much closer than ever. I've added your formulas and they work but when you add 'Yes' to a cell it formats that row as needed but it also formats the row above with a different, not specified format!

How do you fix this?

Cheers
Miguel

 

[Momentman]

Hi

Thanks I'm so much closer than ever. I've added your formulas and they work but when you add 'Yes' to a cell it formats that row as needed but it also formats the row above with a different, not specified format!

How do you fix this?

Cheers
Miguel[/QUOTE

Am afraid I dont quite get the Problem, On my test sheet, when I change a cell in column F to "Yes", it highlights as Yellow and doesn't affect any other cell

Maybe check this, check which cells the conditional formatting applies to(the header row should be excluded), Highlight cells A2 to F4 and then Go to Conditional Formatting and manage rules to check

 

[Miguelluis]

It was user error. I had a row that kept being updated as my base, fixed it now (I think!)

Thank you so much for your help.

Miguel

 

[Momentman]

Glad its working now