VBA for conditional formatting using three colors

[declanGISS]

I was wondering if there is a simple VBA to conditional format a large number of cells based on the cell values.

Essentially I would want any cell less than 2 to be red, cells from 2-4 could be orange and cells greater than 4 would be red. I am sure it is relatively easy, I am just drawing a blank right now.

 

[Zot]

It can be done but you must state the condition how you'd want it to happen.

Does it involve specific range of cells?
Does the color change after entering number?

Need to explain what steps took place to get the condition. I'm sure many can help you with that.
Providing sheet capture would help too.

 

[cmowla]

This should work:

Sub Conditional_Formatting_Test()

Dim sheetNameWhereRangeToBeFormattedIs As String
sheetNameWhereRangeToBeFormattedIs = ActiveSheet.name

Dim formatted_Range As Range
Set formatted_Range = Sheets(sheetNameWhereRangeToBeFormattedIs).Range("A1:G10")

'First delete the formatting conditions for the range to start fresh.
formatted_Range.FormatConditions.Delete

'Now create the formatting rules.
With formatted_Range
    .FormatConditions.Add(Type:=xlCellValue, operator:=xlLess, Formula1:="2").Interior.color = RGB(255, 0, 0) 'red
    .FormatConditions.Add(Type:=xlCellValue, operator:=xlBetween, Formula1:="2", Formula2:=4).Interior.color = RGB(255, 165, 0) 'orange
    .FormatConditions.Add(Type:=xlCellValue, operator:=xlGreater, Formula1:="4").Interior.color = RGB(255, 0, 0) 'red
End With

End Sub