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.

because the script have other functions and this is just a part of it — Molham Al Nasr
– Molham Al Nasr, Commented Nov 27, 2020 at 10:46

FaneDuru · Accepted Answer · 2020-11-27 11:10:47Z

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

answered Nov 27, 2020 at 11:10

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Apply Conditional Formatting to Columns Based on Cell Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related