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.

 

[adam087]

Have you tried doing this with Conditional Formatting?

/AJ

 

[Wingfoot]

Hi - yes as I said I realise that this can be done through using conditional formatting but would like to know how to achieve it using VBA as well.

Thanks

 

[adam087]

Ooops sorry. Should have read the full question. Welcome to the board btw.

Perhaps something like this?

Option Explicit

Public Sub ColourCells()


    Dim fullRange As Range
    Dim cell As Range
    
    Set fullRange = Sheets("Sheet1").Range("A1:B10")  '<--- Change as required, the area that will be checked
    
    For Each cell In fullRange
    
        With cell
            If Not Application.WorksheetFunction.IsNumber(.Value) Then
                .Interior.Color = RGB(255, 255, 255)
        
            Else
                Select Case .Value
                Case Is > 0
                     .Interior.Color = RGB(255, 0, 0)
                Case Is < 0
                     .Interior.Color = RGB(0, 255, 0)
                Case Else
                     .Interior.Color = RGB(0, 0, 255)
                End Select
            
            End If
        
        End With
    
    Next cell
    
End Sub

There will be other methods I'm sure.

/AJ

 

[par60056]

I had almost the same code as Adam but I did it with the worksheet change event so that it changed the color any time a value was changed.

Private Sub Worksheet_Change(ByVal Target As Range)


For Each thecell In Target
    If (Not IsEmpty(thecell)) Then
        With thecell
            If .Value > 0 Then
                .Interior.Color = RGB(255, 0, 0)
            ElseIf .Value < 0 Then
                .Interior.Color = RGB(0, 255, 0)
            Else
                .Interior.Color = RGB(255, 255, 255)
            End If
        End With
    Else
        thecell.Interior.Color = RGB(255, 255, 255)
    End If
Next
End Sub

 

[Wingfoot]

Thanks very much to both of you - they work really well.

 

[adam087]

Cool. Thanks for the feedback.

/AJ

 

[Wingfoot]

Hi

Just off the back of this query - is it possible to modify this code so that it can be used as a personal macro within any worksheet/workbook, where once the code is run, the target cells have the conditional formatting re-applied each time one of them is modified?

I need to be able to run this from personal macro book so it can set on any range, as oppose to the code being set in a specific sheet.

Thanks

 

[par60056]

If you want to put in in the personal, you will need to name it rather than have it be an event procedure.

I assume you would want to apply it to the selected cells. So you would make this slight modification:

For Each thecell In [COLOR=#ff0000]Selection[/COLOR]
    If (Not IsEmpty(thecell)) Then
        With thecell
            If .Value > 0 Then
                .Interior.Color = RGB(255, 0, 0)
            ElseIf .Value < 0 Then
                .Interior.Color = RGB(0, 255, 0)
            Else
                .Interior.Color = RGB(255, 255, 255)
            End If
        End With
    Else
        thecell.Interior.Color = RGB(255, 255, 255)
    End If
Next

 

[Wingfoot]

Hi

Thanks for your reply.

With that change though, the macro will still need to be run manually each time the cells in the selection are changed instead of automatically re-formatting on the basis of the cell value each time the cells in the selection are updated e.g. having made my selection and run the macro, if I subsequently change one of the cells within the selection from 5 to -2, I would want it to change from red to green automatically (without me re-running the macro).

Thanks