VBA code to make negative numbers red and positive numbers black

[commonproblems]

Hi all,

I'm trying to write code that allows me to make negative numbers red and positive numbers black for a highlighted range of cells.

it seems to work based on the first cell, so if the first cell is negative all numbers go red and if the first number is positive all numbers go black.
I've pasted the code below, if someone would be able to suggest a fix. Thank you


Sub Decimalworkswith2decimalplaces()
'
' Decimalworkswith2decimalplaces Macro
' decimal works with 2 decimal places
'
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* """"-""""??_);_(@_)"
If ActiveCell.Value < 0 Then

With Selection.Font
.Color = 255
End With

End If

End Sub

 

[My Aswer Is This]

So you want to select a range and then run the script and have these results. Is that what you want.

If not what is the range you want?

 

[commonproblems]

Yes that is correct - I'd like to select the range and then run the script.

thank you

 

[jolivanes]

Sub One_Way()
Dim c As Range
    For Each c In Selection
        If c.Value < 0 Then c.Font.Color = vbRed
    Next c
End Sub

It assumes that your regular font color is black. In other words, it does not change the values equal to or more than zero.

 

[offthelip]

why do it with vba when you can do it simply by formatting the cells , select the cells select format number and expand the box and then select negative numbers as RED

 

[My Aswer Is This]

Looks like you already have other answers now.
But this was mine.

Sub Color_My_Selection()
'Modified 11/8/2018 6:17:45 PM  EST
Application.ScreenUpdating = False
Dim r As Range
For Each r In Selection
    If r.Value < 0 Then r.Font.Color = 255
Next
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

No need to select anything if you use conditional formatting.
https://trumpexcel.com/negative-numbers-red-excel/