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 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
JohnD1954 unfortunately it doesn't work.
it needs a secondary code to limit range plus it isn't being selective as it's highlighting everything even those that don't fit range.The formulas should not have quotes around them, and begin with =
For example, =TODAY()+30 instead of "TODAY()+30"