Hello guys.
I have a scenario where i need to set the interiour color of all cells in a sheet according to it's values.
The correct color is stored in a list on another sheet and the value that represent the color is loaded into an array along with the colors long value.
It works pretty well, except it won't set the color on cells that contain a formula instead of a value (despite lookin:=xlValues).
Can you spot what might be wrong at the first glance of would you like me to attach the xlsx file?
I don't understand why the code keeps ignoring it, despite that the fact that the FIND method is indeed finding the value.
Sub DoColors_v2()
Dim Picker As Variant
Dim Rws As Long, j As Long
Dim i As Integer
Dim k As Integer
Dim c As Range
Dim FirstAddress
Application.StatusBar = "Coloring..."
Application.ScreenUpdating = False
'load search strings and colors into Picker array
With Worksheets("Colors").Range("A2:B257")
ReDim Picker(1 To .Rows.Count, 1 To 2)
For i = 1 To .Rows.Count
Picker(i, 1) = .Cells(i, 1).Value
Picker(i, 2) = .Cells(i, 2).Value
Next i
End With
'search the test range, changing backgrounds as required
With Worksheets("Setup 1").Range("A3:AA2000")
For k = 1 To UBound(Picker)
With Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
Set c = .Find(Picker(k, 1), After:=Range("A3"), SearchOrder:=xlByRows, SearchDirection:=xlNext, LookIn:=xlValues, MatchCase:=False, Lookat:=xlWhole, SearchFormat:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.Color = Picker(k, 2)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next k
End With
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub
Bookmarks