Conditional Formatting VBA Cells Colour based on the Cells contents

Question

my VBA skills aren't the best so bear with me. I want to format the fill colour of cells based on their values. I know how to do this using Conditional Formatting, however this takes a bit of time to do. Can anyone share some VBA code to automate this process?

I want the cells with values less than 100 to have a green fill colour, the cells greater than 100 but less than 2500 to be yellow and anything greater than this to be red?

Your help would be much appreciated. Thanks Ian

Chris Mack Chris Mack · Accepted Answer · 2016-08-10T20:09:19

This will do it (you will probably need to change your range - I've used A1:A3 for this example):

Sub ColorCells()

    Dim rng As Range, cell As Range

    Set rng = ActiveSheet.Range("A1:A3")

    For Each cell In rng

        If cell < 100 Then
            cell.Interior.Color = RGB(0, 255, 0)
        ElseIf cell < 2500 Then
            cell.Interior.Color = RGB(255, 255, 0)
        ElseIf cell >= 2500 Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If

    Next

End Sub

Conditional Formatting VBA Cells Colour based on the Cells contents

1 Answers