Changing Cell Properties when sheet is protected

[Simmer2]

Would like to change a cells backgound color based on the value of the cell
using Vba.
If the value in cell is > 0 then turn it green.
If value in cell is < 0 then turn it red.
Would like to have the sheet protected to guard against user changes to cell
formulas.
Using EXCEL 2003.
Any help much appreciated.

 

[JMB]

Conditional formatting is tailor made for this operation, but if you must use
VBA...

Any particular cell(s) or just any cell on the worksheet? Do you want to
color the cells as the data is entered or is the data already entered and you
want to color the cells after the fact?

If you right click on the sheet tab, select view code, and paste this into
the code window, it should shade any cell on the sheet as data is entered
into it (assuming you're using the default color palette):

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo CleanUp
Application.EnableEvents = False

With Target
If IsNumeric(.Value) Then
If .Value < 0 Then
.Interior.ColorIndex = 3
ElseIf .Value > 0 Then
.Interior.ColorIndex = 10
End If
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub