CODEWITHSUNDEEP
excelvba
Harrison Paradise
Harrison Paradise

Reputation: 13

Highlighting negative values within a specific column in Excel

For some reason, this code is highlighting the entire M column. I am very new to VBA, sorry if this is obvious. How can I get it to highlight just the negative values in the column? 

Sub HighlightNegatives()

    Dim rCell As Range
    Dim rng As Range   

    Set rng = Range("M:M")

        For Each rCell In rng.Cells            
            If rCell.Value < 0 Then
                rng.Font.Color = RGB(255, 0, 0)
                rng.Font.Bold = True
            End If
        Next
End Sub

Upvotes: 1

Views: 624

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6388

That's because you change the colour and font of the entire column M (rng) you want to change the colour and font of each individual cell (rCell) instead:

Sub HighlightNegatives()

Dim rCell As Range
Dim rng As Range

Set rng = Range("M:M")
    For Each rCell In rng.Cells
        If rCell.Value < 0 Then
            rCell.Font.Color = RGB(255, 0, 0)
            rCell.Font.Bold = True
        End If
    Next
End Sub

That said. Is there a reason you can't use conditional formatting for this?

Upvotes: 2

Related Questions