Forum Discussion
Solved
create VBA function to highlight dates
I have a spreadsheet that needs to have a date function inserted. Our business has to do reports for clients on a regular basis and I need to highlight upcoming report dates for specific clients. I'm not a regular user of VBA so it is hard for me even when it may be the simplest of solutions for someone else.
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.
- Hi JohnD,
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.. 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.
