conditional formatting vba with if

Question

I have a question: I'm trying to make two different conditional formatting at once. But it works only the second one. Im using if and Elseif, and working only endif What i have to write to get work them both? First one after that another?

 For Each cell In Range("A1:AA1")

   If cell.Value = "GM WP6 Sensor Status" Then

Cells.FormatConditions.Delete
    Columns("H:H").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=32671"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    'Selection.FormatConditions(1).StopIfTrue = False
    Columns("H:H").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(H1))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1").Select

''''''''''''''''''''''''''Tikrina kita


ElseIf cell.Value = "GM WP6 Sensor Status light" Then

Cells.FormatConditions.Delete
   Columns("I:I").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
       Formula1:="=32767"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("I:I").Select
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(I1))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1").Select
   End If
Next cell

Subodh Tiwari sktneer · Accepted Answer · 2017-08-08 12:42:31Z

1

Please give this a try...

Sub ConditionalFormatting()

Columns("H:H").FormatConditions.Delete
Columns("I:I").FormatConditions.Delete

If Application.CountIf(Range("A1:AA1"), "GM WP6 Sensor Status") > 0 Then
    Columns("H:H").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(LEN(TRIM(H1))>0,H1<>32671)"
    Columns("H:H").FormatConditions(Columns("H:H").FormatConditions.Count).SetFirstPriority
    With Columns("H:H").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
End If

If Application.CountIf(Range("A1:AA1"), "GM WP6 Sensor Status light") > 0 Then
    Columns("I:I").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(LEN(TRIM(I1))>0,I1<>32671)"
    Columns("I:I").FormatConditions(Columns("I:I").FormatConditions.Count).SetFirstPriority
    With Columns("I:I").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
End If
End Sub

answered Aug 8, 2017 at 12:42

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

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

2 Comments

Skirmante Valentaite Over a year ago

Run time error 5. Invalid procedure call or argument . At this : Columns("H:H").FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(LEN(TRIM(H1))>0,H1<>32671)"

Skirmante Valentaite Over a year ago

i make some corrections its working now :) thanks @sktneer

Collectives™ on Stack Overflow

conditional formatting vba with if

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related