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.
I concur with others, while I am an avid VBA User, you should be able to do this without. I will say this about excel, columns are your friend! what I mean is, there are 16000 columns on an excel worksheet.. I have yet to see a data set that has 16000 columns. Utilize them to build your indicators. one column for past due, one column for almost due (0-30 days) and one column for okay.
Then you can take a fourth column to put an if statement in to identify which one it is with a letter.
do your conditional formatting off the column with the If and then Hide those columns.
It is not nearly as elegant as doing the entire formula in conditional formatting (which you should be able to work out) but it's a heck of a lot easier to understand..