Highlighting negative values within a specific column in Excel

Question

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

This looks like a job for... conditional formatting? – Mathieu Guindon Oct 03 '19 at 21:10 — Mathieu Guindon, Oct 03 '19 at 21:10

score 2 · Accepted Answer · answered Oct 03 '19 at 21:03

2

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?

answered Oct 03 '19 at 21:03

cybernetic.nomad

6,100
3
18
31

Thank you so much for this. I am making this alongside some other macros for a sheet I download and format every day. This way I can make the process faster instead of conditional formatting each and every day. – Harrison Paradise Oct 03 '19 at 21:22
FYI, you can add conditional formatting with VBA. See [this](https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code) – cybernetic.nomad Oct 03 '19 at 21:31

Highlighting negative values within a specific column in Excel

1 Answers1