How to Apply Conditional Formatting to Columns Based on Cell Value

Question

in the following VBA-Code i'm trying to give the Cells in Range("H1:IV1") different colors based on the content of the Cell

Sub AddColor()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("H1:IV1")

For Each cel In SrchRng
    If InStr(1, cel.Value, "|unsichtbar|") > 0 Then
        cel.Interior.Color = vbRed
    End If
    If InStr(1, cel.Value, "|lesen|") > 0 Then
        cel.Interior.Color = vbYellow
    End If
    If InStr(1, cel.Value, "|schreiben|") > 0 Then
        cel.Interior.Color = vbGreen
    End If
Next cel

End Sub

my questions are:

1- How can i set the Variable "SrchRng" to search in first Row not just in Range("H1:IV1")?

2- The changing color Code works good on the cell but i need to apply the color on the Column where the if-statement true.

Answer 1

Please, test the next code:

Sub AddColor()
 Dim SrchRng As Range, cel As Range, sh As Worksheet
 Dim lastCol As Long, lastR As Long

 Set sh = ActiveSheet      'use here the needed sheet
 lastCol = sh.cells(1, Columns.count).End(xlToLeft).Column 'last column
 lastR = sh.UsedRange.rows.count                           'last row

 Set SrchRng = sh.Range("A1", sh.cells(1, lastCol)) 'the range to be searched

 For Each cel In SrchRng
    If InStr(1, cel.Value, "|unsichtbar|") > 0 Then
        sh.Range(cel, sh.cells(lastR, cel.Column)).Interior.Color = vbRed
    ElseIf InStr(1, cel.Value, "|lesen|") > 0 Then
        sh.Range(cel, sh.cells(lastR, cel.Column)).Interior.Color = vbYellow
    ElseIf InStr(1, cel.Value, "|schreiben|") > 0 Then
        sh.Range(cel, sh.cells(lastR, cel.Column)).Interior.Color = vbGreen
    End If
 Next cel
End Sub