CODEWITHSUNDEEP
vbaexcel
Fizza1
Fizza1

Reputation: 13

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

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

Upvotes: 1

Views: 1097

Answers (3)

YasserKhalil
YasserKhalil

Reputation: 9548

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

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Remove Exit For. It exits the for - loop:

Excel VBA - exit for loop

Upvotes: 1

Related Questions