Highlight Row depending on two separate cells

Miguelluis

New Member
Joined
Jan 29, 2013
Messages
45
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.


ABCDF
TestTestTest12/09/14Yes
TestTestTest01/09/14No
TestTestTest05/08/14Yes

<tbody>
</tbody>

Thanks
Miguel
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top