Highlighting Holidays in a Range with dates

[Guest]

Hello Guys,

I Have dates in the range D3369, from 1 jan to 31 dec (in that format).
Now I want to highlight the holidays in that range, but i'm having
difficulties with that.
I've created a collection, en i'm trying to use a For Each... Next to loop
through the range.

I also want this procedure to start automatically when i open up the
workbook, so in module 1 this doesn't seem to work, and I have no idea where
en how i should get this done...

What i've tried:

Sub Toef()
Dim hCollection As New Collection
Dim newyear, easter, daym, ascension, ... As String
Dim DateRange As Date
Dim allSheets As Worksheets
DateRange = allSheets.range("D3369").Select
newyear= "1 jan"
easter= "16 apr"
daym= "1 may"
ascension= "24 mei"
....
hCollection.Add (newyear)
hCollection.Add (easter)
hCollection.Add (daym)
hCollection.Add (ascension)
....

For Each DateRange In hCollection
DateRange.Item.Color = RGB(0, 255, 0)
Next DateRange.Item
End Sub

Thanks in advance guys,

Memento

 

[Guest]

A slightly different approach to yours. Your dates in A1 - A365 and you
holiday list in B1 - B8. then


Sub highlight()
Dim MyRange As Range
Set MyRange = Range("a1:a365")
For Each datecell In MyRange
datecell.Select
For x = 1 To 8
y = Cells(x, 2).Value
If datecell.Value = Cells(x, 2).Value Then
ActiveCell.Interior.ColorIndex = 7
End If
Next
Next
End Sub

Mike

 

[Guest]

Memento,

I like this one a bit better:-

Sub highlight()
Dim MyRange As Range
Dim HolRange As Range
Set MyRange = Range("a1:a365")
Set HolRange = Range("B1:B13")
For Each datecell In MyRange
datecell.Select
For Each holcell In HolRange
If datecell.Value = holcell.Value Then
ActiveCell.Interior.ColorIndex = 7
End If
Next
Next
End Sub

Mike

 

[tissot.emmanuel]

Hi,

You may use conditionnal formatting this way:

Sub HighLight()
With Range("D3369")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=COUNT.IF("$H$3:$H$6";D3)=1"
.FormatConditions(1).Interior.Color = RGB(0, 255, 0)
End With
End Sub

Of course you need a range to store your holiday date (H3:H6 here).

It should be faster than a For Each Next statement.

Best regards from France,

Manu/

 

[Bob Phillips]

Why not just use Conditional Formatting without VBA

Just use a formula of

=ISNUMBER(MATCH(D3,holiday_list,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)