SOLVED

create VBA function to highlight dates

%3CLINGO-SUB%20id%3D%22lingo-sub-3374486%22%20slang%3D%22en-US%22%3Ecreate%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374486%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20needs%20to%20have%20a%20date%20function%20inserted.%20Our%20business%20has%20to%20do%20reports%20for%20clients%20on%20a%20regular%20basis%20and%20I%20need%20to%20highlight%20upcoming%20report%20dates%20for%20specific%20clients.%20I'm%20not%20a%20regular%20user%20of%20VBA%20so%20it%20is%20hard%20for%20me%20even%20when%20it%20may%20be%20the%20simplest%20of%20solut%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dates%20screenshot.jpg%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371645i96D2948B939584E4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22dates%20screenshot.jpg%22%20alt%3D%22dates%20screenshot.jpg%22%20%2F%3E%3C%2FSPAN%3Eions%20for%20someone%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3374486%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374516%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627081%22%20target%3D%22_blank%22%3E%40JohnD1954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20may%20be%20possible%20to%20do%20this%20without%20VBA%2C%20using%20conditional%20formatting%20instead.%20Could%20you%20tell%20us%20which%20cells%20you%20want%20to%20be%20highlighted%2C%20and%20under%20which%20conditions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374519%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374519%22%20slang%3D%22en-US%22%3EI've%20tried%20conditional%20formatting%20and%20there%20isn't%20enough%20flexibility.%3CBR%20%2F%3EIt%20takes%2030%20days%20to%20set%20up%20all%20the%20different%20responses%20required%20to%20do%20a%20report%20so%20I%20need%20to%20know%2030%20days%20ahead%20of%20time%20of%20any%20report%20coming%20due.%20I%20also%20need%20to%20know%20of%20any%20dates%20that%20come%20short%20of%20that%20time%20period%2C%20i.e.%2C%20somewhere%20in%20between%20the%2030%20days%20and%20due%20date.%20Third%20option%20needs%20to%20be%20where%20a%20report%20is%20past%20due.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374525%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627081%22%20target%3D%22_blank%22%3E%40JohnD1954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shouldn't%20be%20difficult%20to%20do%20that%20using%20conditional%20formatting.%3C%2FP%3E%0A%3CP%3ESelect%20the%20entire%20range%20with%20the%20dates%20that%20you%20want%20to%20highlight.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20drop-down%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'equal%20to'%20from%20the%20second%20drop-down.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DTODAY()%2B30%20in%20the%20box%20next%20to%20it.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color%2C%20for%20example%20yellow.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20'less%20than'%20instead%20of%20'equal%20to'.%3C%2FP%3E%0A%3CP%3EUse%20the%20same%20formula%20%3DTODAY()%2B30%20and%20select%20a%20different%20color%2C%20for%20example%20orange.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%2C%20repeat%20them%20again%2C%20with%20'less%20than'.%3C%2FP%3E%0A%3CP%3EUse%20the%20formula%20%3DTODAY()%20and%20select%20yet%20another%20color%2C%20for%20example%20red.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374526%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374526%22%20slang%3D%22en-US%22%3EI%20shall%20try%20and%20let%20you%20know.%20I%20didn't%20know%20that%20you%20can%20set%20up%20conditional%20formatting%20more%20than%20once%20on%20the%20same%20selection.%20Thank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374739%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627081%22%20target%3D%22_blank%22%3E%40JohnD1954%3C%2FA%3E%26nbsp%3Bunfortunately%20it%20doesn't%20work.%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Format1.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371651i4E7C9B68212978D2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Conditional%20Format1.jpg%22%20alt%3D%22Conditional%20Format1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3Eit%20needs%20a%20secondary%20code%20to%20limit%20range%20plus%20it%20isn't%20being%20selective%20as%20it's%20highlighting%20everything%20even%20those%20that%20don't%20fit%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375023%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627081%22%20target%3D%22_blank%22%3E%40JohnD1954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formulas%20should%20not%20have%20quotes%20around%20them%2C%20and%20begin%20with%20%3D%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20%3DTODAY()%2B30%20instead%20of%20%22TODAY()%2B30%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375833%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375833%22%20slang%3D%22en-US%22%3EI%20didn't%20insert%20the%20quotes%2C%20purely%20Excel%20function%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375845%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627081%22%20target%3D%22_blank%22%3E%40JohnD1954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20added%20the%20quotes%2C%20but%20that%20means%20the%20rule%20won't%20work%20as%20intended.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375847%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375847%22%20slang%3D%22en-US%22%3Ethat's%20why%20I'm%20thinking%20it%20may%20be%20better%20and%20more%20comprehensive%20to%20use%20VBA.%20Unfortunately%2C%20I%20don't%20know%20much%20about%20coding.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376181%22%20slang%3D%22en-US%22%3ERe%3A%20create%20VBA%20function%20to%20highlight%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376181%22%20slang%3D%22en-US%22%3EHi%20JohnD%2C%3CBR%20%2F%3EI%20concur%20with%20others%2C%20while%20I%20am%20an%20avid%20VBA%20User%2C%20you%20should%20be%20able%20to%20do%20this%20without.%20I%20will%20say%20this%20about%20excel%2C%20columns%20are%20your%20friend!%20what%20I%20mean%20is%2C%20there%20are%2016000%20columns%20on%20an%20excel%20worksheet..%20I%20have%20yet%20to%20see%20a%20data%20set%20that%20has%2016000%20columns.%20Utilize%20them%20to%20build%20your%20indicators.%20one%20column%20for%20past%20due%2C%20one%20column%20for%20almost%20due%20(0-30%20days)%20and%20one%20column%20for%20okay.%3CBR%20%2F%3EThen%20you%20can%20take%20a%20fourth%20column%20to%20put%20an%20if%20statement%20in%20to%20identify%20which%20one%20it%20is%20with%20a%20letter.%3CBR%20%2F%3Edo%20your%20conditional%20formatting%20off%20the%20column%20with%20the%20If%20and%20then%20Hide%20those%20columns.%3CBR%20%2F%3EIt%20is%20not%20nearly%20as%20elegant%20as%20doing%20the%20entire%20formula%20in%20conditional%20formatting%20(which%20you%20should%20be%20able%20to%20work%20out)%20but%20it's%20a%20heck%20of%20a%20lot%20easier%20to%20understand..%3C%2FLINGO-BODY%3E
Occasional Contributor

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 solutdates screenshot.jpgions for someone else.

10 Replies

@JohnD1954 

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.
best response confirmed by JohnD1954 (Occasional Contributor)
Solution

@JohnD1954 

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. 
Conditional Format1.jpg
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.

@JohnD1954 

The formulas should not have quotes around them, and begin with =

For example, =TODAY()+30 instead of "TODAY()+30"

I didn't insert the quotes, purely Excel function

@JohnD1954 

Excel added the quotes, but that means the rule won't work as intended.

that's why I'm thinking it may be better and more comprehensive to use VBA. Unfortunately, I don't know much about coding.
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..