May 14 2022 02:43 AM
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.
May 14 2022 02:47 AM
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?
May 14 2022 02:52 AM
May 14 2022 03:00 AM
SolutionIt 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.
May 14 2022 03:02 AM
May 14 2022 03:18 AM
@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.
May 14 2022 03:33 AM
The formulas should not have quotes around them, and begin with =
For example, =TODAY()+30 instead of "TODAY()+30"
May 14 2022 02:28 PM
May 14 2022 02:37 PM
Excel added the quotes, but that means the rule won't work as intended.
May 14 2022 02:45 PM
May 15 2022 12:00 AM
May 14 2022 03:00 AM
SolutionIt 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.