VBA Request

[DRWonoski]

Hello Everyone,

I'm new to vba and need a little help getting certain cells formatted based on their date in number value. I basically have a large calendar displayed in row format as shown below.

The numbers displayed are actually number values associated with the date (ie Oct 1 is actually 41913, Oct 2 is 41914 etc)

I have list created on a different sheet (named Reference) which contains two dates per month also in Number form (41916 for example).

Is there a vba code that I can use to format the dates found in reference to display with a thick border on the calendar or better yet, to format the cells below that date in the calendar to have a thick border?

Essentially, each year I would like to just have to update that reference sheet with the new dates and have it automatically format the corresponding cell to look like the below image.

 

[MickG]

Try running this code in the Calendar sheet.
Dates to format in sheet "Reference" column "A"

[COLOR="Navy"]Sub[/COLOR] MG25Aug36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.UsedRange
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] IsDate(Dn.Value) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Reference")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    .Item(Dn.Value).Offset(1).Borders.Weight = 4
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[DRWonoski]

Hi Mick,

Thank you for the quick response. I tested out what you said to do and unfortunately nothing happened. I tried adjusting the Range to match what I had but failed. I'm assuming it may have something to do with how I'm calling the cells in the calendar. I have attached a copy of my reference sheet so you can see the "Schedule" area which has the range, again converted to those numbers, and a copy of the formula I'm using on the calendar to create the dates. Any advice here? Thanks again

 

[MickG]

If you could send a copy of your file using "Box.com" (free file sharing) it would be easier to work with you data, a picture means that I have to manually reconstruct your file to be able to work with it.

 

[DRWonoski]

Unfortunately I can't upload anything from work except images