Formatting Cells that contain Matches to Data in a List

  • Thread starter Thread starter John Pierce
  • Start date Start date
J

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.
 
Something like (untested):

Sub FormatMatchingDates()
Dim c As Range, cDates As Range 'cDates is the column of 300 dates
Set cDates = Sheet2.Range("D1:D300") '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
 
Back
Top