Formatting Cells that contain Matches to Data in a List

[John Pierce]

The small procedure below works perfectly at changing the format of
any number of cells that contain data that matches that in a few
specific cells.

Sub FormatMatchingDates()
Dim c As Range
For Each c In Sheet1.Range("A1:BB78")
If c.Value = Date1 Or c.Value = Date2 Or c.Value = Date3 Then
With c
.Font.Size = 10
.Font.Bold = True
.Interior.ColorIndex = 4
End With
End If
Next
Exit Sub
End Sub

However, the problem has changed. I now have almost 300 dates to match.
They are arranged in a single column so I can find the matches with a
VLookup formula in a column next to my test data but that just isn't
a good solution. It really needs to be highlighted with formatting.
There are some blank cells among the "c" in Sheet1.Range("A1:BB78").
I tried something with Match to test it for being TRUE rather than
using the location it returns, but it didn't work for me. Any help
would be greatly appreciated.

 

[Vasant Nanavati]

Something like (untested):

Sub FormatMatchingDates()
Dim c As Range, cDates As Range 'cDates is the column of 300 dates
Set cDates = Sheet2.Range("D1300") 'or whatever location
For Each c In Sheet1.Range("A1:BB78")
If Not cDates.Find(c) Is Nothing Then
With c
.Font.Size = 10
.Font.Bold = True
.Interior.ColorIndex = 4
End With
End If
Next
Exit Sub
End Sub

 

[John Pierce]

Vasant,
Thanks for your help. It does the job perfectly!
John

 

[Vasant Nanavati]

You're welcome, John!