Vba code to format cell colours: red for greater than 0, green for less than 0

[Wingfoot]

Hi

I've only just joined the forum and I'm new to VBA. Please could somebody advise what is wrong with my code below which I want to automatically colour any cell in my worksheet according to the rule above.

Sub CondFormatZero()

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65280
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    MyCell = ActiveCell.Address
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(MyCell>0,1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

I realise this can be achieved using conditional formatting but would be useful to know how to do as I am a beginner in VBA.

Thanks in advance.

 

[par60056]

SO what you want is a macro to apply the conditional formatting to the selected cells.

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 255, 0)
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[Wingfoot]

Basically yes - thanks very much