Forum Discussion
create VBA function to highlight dates
It shouldn't be difficult to do that using conditional formatting.
Select the entire range with the dates that you want to highlight.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'equal to' from the second drop-down.
Enter the formula =TODAY()+30 in the box next to it.
Click Format...
Activate the Fill tab.
Select a color, for example yellow.
Click OK, then click OK again.
Repeat the above steps, but with 'less than' instead of 'equal to'.
Use the same formula =TODAY()+30 and select a different color, for example orange.
Finally, repeat them again, with 'less than'.
Use the formula =TODAY() and select yet another color, for example red.
It may be possible to do this without VBA, using conditional formatting instead. Could you tell us which cells you want to be highlighted, and under which conditions?
- I've tried conditional formatting and there isn't enough flexibility.
It takes 30 days to set up all the different responses required to do a report so I need to know 30 days ahead of time of any report coming due. I also need to know of any dates that come short of that time period, i.e., somewhere in between the 30 days and due date. Third option needs to be where a report is past due.It shouldn't be difficult to do that using conditional formatting.
Select the entire range with the dates that you want to highlight.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'equal to' from the second drop-down.
Enter the formula =TODAY()+30 in the box next to it.
Click Format...
Activate the Fill tab.
Select a color, for example yellow.
Click OK, then click OK again.
Repeat the above steps, but with 'less than' instead of 'equal to'.
Use the same formula =TODAY()+30 and select a different color, for example orange.
Finally, repeat them again, with 'less than'.
Use the formula =TODAY() and select yet another color, for example red.
- I shall try and let you know. I didn't know that you can set up conditional formatting more than once on the same selection. Thank you
