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.