Highlighting dates

[Zycho]

Hi! Question.. I have got store list on B3:B7, dates when the vehicle changes for the store on D3:D7, vehicle registrations drop down list on F3:F7 and when the next change is due required on H3:H7.. now what I want to do is if the same vehicle is used for the same store 4 days in a row to be highlighted red in the next change due row and no change at all if no vehichle is selected. I can't work it out how to do that as I'm pretty new at doing all that on excel. Thanks in advance!

 

[PhilS2520]

Hi Zycho,

Look into Conditional Formatting, and see what you can do with it.

 

[Zycho]

Can you be a little more specific what to look for please?

 

[RasGhul]

It will help us build a solution if you can send or post some sample data Zycho. Use google docs or Dropbox etc if you want to send your sample sheet.

 

[Zycho]

https://drive.google.com/file/d/1o8jaH5q-mNnLUer1PCOG78bFMafNn1Ti/view?usp=drivesdk

 

[Zycho]

Bump

 

[RasGhul]

So just to confirm you will have the vehicle registrations as drop-down in the Change Due column as well, and thats when the color comes in?

 

[Zycho]

So just to confirm you will have the vehicle registrations as drop-down in the Change Due column as well, and thats when the color comes in?

All I want is the due date to change red when the same vehicle is used 4 days in a row.

 

[RasGhul]

The following works for me assuming that the dates are from the current date to 4 days ago,

Next to the Change Due column copy and drag down 50 rows or more;

=IF(COUNTIFS($D$3:$D$12,">="&(TODAY()-3),$D$3:$D$12,"<="&(TODAY()),$B$3:$B$12,LOOKUP(2,1/(B:B<>""),B:B),$H$3:$H$12,H3)=4,COUNTIFS($D$3:$D$12,">="&(TODAY()-3),$D$3:$D$12,"<="&(TODAY()),$B$3:$B$12,LOOKUP(2,1/(B:B<>""),B:B),$H$3:$H$12,H3),"")

Highlight the Change Due Column H:H, then Conditional Format, formula rule;

=$I1=4

This highlights all the vehicles including the one that has been selected.

 

[Zycho]

Don't seem to work... Don't know if I am doing something wrong or.. Sorry to be a pain.