The calculate event does not have an argument, so if you left the ByRef
Target bit in, that is your error.

You should also realise that this event gets triggered by any
re-calculation, you cannot monitor it to one particular cell, so the code
has to change as well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Adam" wrote in message
...
OK, I see.

When I move this to worksheet_calculate event, I receive a compile error
about a procedure declaration. Do you have any insight.

Thanks,

Adam

----- Frank Kabel wrote: -----

Hi
worksheet_change is NOT triggered by a formula change. You may use
the
worksheet_calculate event for this

--
Regards
Frank Kabel
Frankfurt, Germany

"Adam" schrieb im Newsbeitrag
...
Hi -
I use the following to change the background color of a cell. Is
worksheet_change triggered with changes in a value returned by a
vlookup? My worksheet does not update until I go into the individual
cell and update the forumla, then the color changes. How would I use
this function? Please help.
Thanks, Adam
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target,
Range("C:C,I:I,O:O,U:U,AA:AA,AG:AG"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "T-1": Num = 6 'yellow
Case Is = "T-2": Num = 10 'green
Case Is = "T-3": Num = 5 'blue
Case Is = "SALE": Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub