'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'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.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"rmayer" wrote in message
...
I have values created by a formula from info pasted into other cells
in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF
formulas use ="","", to present a blank cell if no data is available.
In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result
to a report cover page. I use CF to change the cell background color
in G29 to G36 and I29 to I36 depending on what values appear there, as
G29 = 95%, green I29 = A, green, etc. It works great, but....
Now I need a fourth color. Add in's are forbidden.
I have VB that will change cell color in G29 to G36, etc, but only
when values are typed directly in these cells. It will not update
G29 when the value in X17 changes. Is there a way to modify it
so that it will copy the value in X17 to G29, etc, and highlite the
cell to a specified color per value ?
Using Office 2000.
Thanks