Hello again David,

I have just realized that I have not been receiving email notifications of
replies to my posts on this forum and have had to do a search on my recent
posts to find them. Hense the late reply. (I've now reported the problem to
Microsoft so hopefully they will fix it.)

However, a couple of observations in your code example.

You should reverse the two lines of code where you assign the active sheet
to a variable and Set the SearchRange. You can then use the worksheet
variable when assigning the range to SearchRange like the following.

Set sh = ActiveSheet
Set SearchRange = sh.Range("C1:Q" & LastRow)

The other thing is to take care when using the color constants to identify a
color. You cannot mix ColorIndex and Color constants as you have done.
ColorIndex and color constants are quite different in VBA. I think that your
line
Case 3, Not vbBlack
should actually be
Case 3, Not xlColorIndexAutomatic

The ColorIndex for black is 1 while the Color Constant vbBlack value is zero.

The ColorIndex values are 1 to 56
plus
xlColorIndexAutomatic with a value of -4105 (when color is set to automatic)
xlColorIndexNone with a value of -4142
See help for more on these. It is highly likely that what you are assuming
is black is actually xlColorIndexAutomatic.

Demonstration:
Set an ActiveCell to font color Automatic and run the following code and
observe the values returned. ColorIndex constants do not match the color
values (or Color constants). ColorIndex constant for Black is 1 while color
value for black is zero (same as vbBlack constant). vbBlack refers to a Color
Constant not ColorIndex.

Sub testFontColor()
'Type anything into the ActiveCell and
'set the font color to Automatic.

MsgBox "ActiveCell.Font.Colorindex is " & _
ActiveCell.Font.ColorIndex & vbCrLf & _
"ActiveCell.Font.Color is " & _
ActiveCell.Font.Color
End Sub

Further demo:
Insert the following code into a blank workbook and it will return all of
the colors for the ColorIndex constants in column A. The row number
represents the ColorIndex. Note that 1 is black.

Column B has the font Colors set to the 8 Color Constant colors with their
constant values and the adjacent column C has the names of the Color
Constant. Note that the values of Colors do not match the values of
ColorIndex.

Sub ColorDemo()
Dim i As Long
'Following sets the interior colors to ColorIndex
'The row number is the ColorIndex for the specified color
'NOTE: Used ColorIndex not color
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i

'Following sets fonts to Color Constants
'with constant value in colored font and
'name of constant adjacent.
'NOTE: Used Color not ColorIndex
Cells(1, 2).Font.Color = vbBlack
Cells(1, 2).Value = vbBlack
Cells(1, 3).Value = "vbBlack"

Cells(2, 2).Font.Color = vbRed
Cells(2, 2).Value = vbRed
Cells(2, 3).Value = "vbRed"

Cells(3, 2).Font.Color = vbGreen
Cells(3, 2).Value = vbGreen
Cells(3, 3).Value = "vbGreen"

Cells(4, 2).Font.Color = vbYellow
Cells(4, 2).Value = vbYellow
Cells(4, 3).Value = "vbYellow"

Cells(5, 2).Font.Color = vbBlue
Cells(5, 2).Value = vbBlue
Cells(5, 3).Value = "vbBlue"

Cells(6, 2).Font.Color = vbMagenta
Cells(6, 2).Value = vbMagenta
Cells(6, 3).Value = "vbMagenta"

Cells(7, 2).Font.Color = vbCyan
Cells(7, 2).Value = vbCyan
Cells(7, 3).Value = "vbCyan"

'Following cell set to black interior otherwise
'white font is not visible.
Cells(8, 2).Interior.Color = vbBlack
Cells(8, 2).Font.Color = vbWhite
Cells(8, 2).Value = vbWhite
Cells(8, 3).Value = "vbWhite"

End Sub

--
Regards,

OssieMac