Is there an alternative way of doing conditional formatting using vba?

[wedd]

Is it possible to perform various calculations highlighting various cells with different colors, based on reults on a specific criteria that each time a figure is added to a column the answer will automate using visual basic, rather than doing conditional formatting? Because I have large sets of data and conditional formatting can be time consuming.


Thanks for your solutions

[Bob Phillips]

Use event code

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Select Case Target

Case 1: Target.Interior.ColorIndex = 5
Case 2: Target.Interior.ColorIndex = 3
'etc

End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.