Conditional Formatting

robstar

New Member
Joined
Jun 16, 2008
Messages
3
I have a large database listing people on an international assignment. I have a column (D) that states their End Date. I would like to turn the whole row of an assignee red (through conditional formatting) if their End Date is past the current date (which I have in cell B2). Can somebody help me with a formula for that. I only know how to do it for a sinlge row!
Thanks!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to the board...

Highlight the ENTIRE range you would like this applied to.
Make note of the Active Cell Row. This is found in the NAME box, to the left of the formula bar.

Click Format - Conditional Formatting
Formula Is
=$D1<$B$2

Replace The 1 with whatever was the Active Cell's Row #.
Also, you don't need to have today's date in a Cell, you can refernce today's date diretly in the conditional formatting

=$D1<TODAY()< p> < TODAY()
Hope This Helps.
 
Last edited:
Upvote 0
Hi jonmo1,

thanks for the quick reply, but it doesn't look as if your tip solves my problem.

I tried the formula on my sheet and put in an Active Cell's Row whose assignment has already ended. But various other rows (which assignment date has not ended yet) turned red as well and I can't see why.

But also with this formula, won't I have to set the formatting for every single row manually?

Thanks
 
Upvote 0
and put in an Active Cell's Row whose assignment has already ended

No, Put in the ACTIVE CELL ROW (Regardless If it is already ended or not).

Follow these steps EXACTLY...

Highlight the ENTIRE RANGE you want this applied to.
Make note of the Active Cell Row #. Found in the NAME box to the left of the formula bar.
Click Format - Conditional Formatting
Select "Formula Is"
Enter the formula
=$D1<TODAY()< p> < TODAY()

change the 1 to whatever was the ACTIVE CELL Row # (Regadless if that row is ended or not)
Click Format
Pick your cell color
Click OK
Click OK

HTH
 
Upvote 0
Wait, $$#$#$#

I always forget that Blanks can will be counted as < Today...
So the formula would be

=AND($D1<>"",$D1<TODAY()< p> < TODAY())
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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