CODEWITHSUNDEEP
excelvba
sbagnato
sbagnato

Reputation: 496

Excel VBA conditional formatting - Removing formatting for 0 value

I have a set of values in a column, 1 NA 0 1 0 NA and 0. I run a successful script on the column to highlight the top value in dark green, and the top 50% of values (excluding the top value) in light green.

Range("K4:K10").Select
Selection.FormatConditions.AddTop10
With Selection.FormatConditions(1)
   .TopBottom = xlTop10Top
   .Rank = 50
   .Percent = True
End With
With Selection.FormatConditions(1).Interior
   .Color = 13434777
End With
Selection.FormatConditions.AddTop10

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
   .TopBottom = xlTop10Top
   .Rank = 1
End With
With Selection.FormatConditions(1).Interior
   .Color = 3394611
End With

That works, but what I want to now do (and this will be added to all columns, most of which have much higher numbers) is remove highlighting from any cell with the value of 0. I tried the below code:

    Dim wsrng As Range
    For Each wsrng In Range("K4:K10")
        If wsrng.Value = 0 Then
            Selection.FormatConditions.Delete
        End If
    Next wsrng

But it is removing the highlighting from every cell in the column regardless of value. Any ideas?

Upvotes: 0

Views: 350

Answers (1)

Vinnie
Vinnie

Reputation: 553

Change selection to wsrng.

Dim wsrng As Range
    For Each wsrng In Range("K4:K10")
        If wsrng.Value = 0 Then
            wsrng.FormatConditions.Delete
        End If
    Next wsrng

Upvotes: 1

Related Questions