How to apply conditional formatting on group of cells by using dat

J

Jon

Hi,
I have a sheet for tracking vacation plan. In this sheet I want to make a
conditional formatting. The sheet contains three columns, the first one is
employee name, and the second one is last working day, the third one is
return to work. I want to apply specific conditional formatting on the three
columns by special conditional as follows:
If the last working day date is over due itself, the all columns to be
filled with color automatically.
Your help is highly appreciated
 
R

Roger Govier

Hi Jon

Mark the range of cells you want to apply the CF to.
Format>Conditional Formatting>use dropdown for Formula is>
=AND($B1<>"",TODAY()-$B1>0)
Choose Format you require.
 
J

Jon

thanks a lost , it works
but for more clarification the B1 is the cell for “Last working Day Dateâ€
 
R

Roger Govier

Hi Jon

Yes, from your description, column B contains the last working day.
The column is Fixed with $B but the row is relative and will change from
1 to 2 etc. as you go down the page.
So if you were looking at row 9, the formula would have become
=AND($B9<>"",TODAY()-$B9>0)

We need the test to see whether column B is empty, otherwise all blank
rows would be formatted with your chosen format as well.
 
Top