Macro to Find the Negative and Positive values

These VBA macros colour the numbers in a selection by sign: negative numbers in red and positive numbers in green, either with a cell fill or with a number format that updates by itself.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Fill negative cells red and positive cells green

Sub HighlightNegPos()
    Dim c As Range
    For Each c In Selection
        If Not IsEmpty(c) And IsNumeric(c.Value) Then
            If c.Value < 0 Then
                c.Interior.Color = RGB(255, 199, 206)
            ElseIf c.Value > 0 Then
                c.Interior.Color = RGB(198, 239, 206)
            End If
        End If
    Next c
End Sub

Zeros, text and blank cells are left as they are.

Show negatives in red with a number format

This doesn’t colour the cell, but negative numbers always appear in red, even after the values change:

Sub RedNegatives()
    Selection.NumberFormat = "#,##0.00;[Red]-#,##0.00"
End Sub

For Indian-style commas, use "[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" for positives and add a red section for negatives.

Without a macro: conditional formatting

  1. Select the numbers.
  2. Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than.
  3. Type 0, pick Light Red Fill, and click OK. Repeat with Greater Than for positives.

Conditional formatting updates automatically when the numbers change. The fill macro colours the cells once.

Things to know

  • The fill colours stay even if a value later changes sign. Re-run the macro, or use conditional formatting for live data.
  • To clear the fills, choose Home → Fill Color → No Fill.

Related macros