For each cell in range if cell is negative then change color

Question

I'm pretty knew to VBA and I'm trying to check if in the specific range of column M if there's a negative number and if it is to change the font to red. This is only working for the first number in my report. I have a feeling that I made a small mistake so please let me know.

Sub Format_M_Column()

    Range("M:M").Select
    With Selection
        Selection.NumberFormat = "0.00"
        .Value = .Value
    End With

    For Each cell In Range("M:M")
        If cell.Value < 0 Then
            cell.Font.ColorIndex = 3
            Exit For
        End If
    Next cell

End Sub

custom format of `0.00;[Red]-0.00` will do it without the need for vba — Scott Craner, Jul 31 '18 at 14:05
I know but I'm trying to make this vba because it'll include other things for my report. — Fizza1, Jul 31 '18 at 14:10
@ScottCraner okay now i see from Peh's example that it could be used in vba. This saves a lost of space. Thank you! — Fizza1, Jul 31 '18 at 14:14

score 1 · Answer 1 · answered Jul 31 '18 at 14:03

1

Remove Exit For. It exits the for - loop:

Excel VBA - exit for loop

answered Jul 31 '18 at 14:03

Vityata

42,633
8
55
100

omg thanks! I was following an answer in this thread and it mentioned to keep it. I'm guessing i wasn't understanding correctly why that specific person had to keep it. – Fizza1 Jul 31 '18 at 14:05

score 1 · Accepted Answer · answered Jul 31 '18 at 14:08

1

as ScottCraner pointed out this should be all you need and should be much faster without that loop.

Sub Format_M_Column()
    With Range("M:M")
        .NumberFormat = "0.00;[Red]-0.00"
        .Value = .Value
    End With
End Sub

answered Jul 31 '18 at 14:08

Pᴇʜ

56,719
10
49
73

score 0 · Answer 3 · answered Jul 31 '18 at 14:10

Try this code (May be faster than yours)

Sub Format_M_Column()
Dim rng As Range
Dim cel As Range

Application.ScreenUpdating = False
    Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(13))

    With rng
        .NumberFormat = "0.00"
        .Value = .Value
        For Each cel In rng
            If cel.Value < 0 Then cel.Font.ColorIndex = 3
        Next cel
    End With
Application.ScreenUpdating = True
End Sub

For each cell in range if cell is negative then change color

3 Answers3