Hi,
I am trying to write some code to find a particular header cell of a column based on its contents and background colour. Once that cell is found i want to scan down the column for particular text and change the font colour if found.
Here is what I have so far:
Private Sub ToggleButton2_Click()
Dim uRng As Range, y As Range, c As Range, col As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set uRng = ws.UsedRange
With Application ' speed up macro, stop screen flickering
.ScreenUpdating = False
.Calculation = xlManual
'With ws
If ToggleButton2.Value = True Then
For Each y In uRng
If y.Interior.ColorIndex = 47 And y = "Status" Then
Set col = y.EntireColumn
For Each c In col
If InStr(UCase(c), UCase("open")) Then
c.Font.Color = 45
End If
Next c
End If
Next y
Else
For Each y In uRng
If y.Interior.ColorIndex = 47 & y = "Status" Then
Set col = y.EntireColumn
For Each c In col
If InStr(UCase(c), UCase("open")) Then
c.Font.Color = 1
End If
Next c
End If
Next y
End If
'End With
.ScreenUpdating = True
.Calculation = xlAutomatic
End With
End Sub
I think it is finding the header of the column but does not seem to be scanning down (possibly a problem with allocating the column as a range?)
I'm pretty new to VBA so any help would be much appreciated.
Bookmarks