Worksheet_Change not updating with vlookup change

[Guest]

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, Ada

Private Sub Worksheet_Change(ByVal Target As Range
Dim Num As Lon
Dim rng As Rang
Dim vRngInput As Varian
Set vRngInput = Intersect(Target, Range("C:C,I:I,O:O,U:U,AA:AA,AG:AG")
If vRngInput Is Nothing Then Exit Su
For Each rng In vRngInpu
'Determine the colo
Select Case rng.Valu
Case Is = "T-1": Num = 6 'yello
Case Is = "T-2": Num = 10 'gree
Case Is = "T-3": Num = 5 'blu
Case Is = "SALE": Num = 3 're
Case Is = 5: Num = 46 'orang
End Selec
'Apply the colo
rng.Interior.ColorIndex = Nu
Next rn
End Sub

 

[Frank Kabel]

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

--
Regards
Frank Kabel
Frankfurt, Germany

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.

 

[Guest]

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

Ada

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

H
worksheet_change is NOT triggered by a formula change. You may use th
worksheet_calculate event for thi

-
Regard
Frank Kabe
Frankfurt, German

Hi

worksheet_change triggered with changes in a value returned by
vlookup? My worksheet does not update until I go into the individua
cell and update the forumla, then the color changes. How would I us
this function? Please help

 

[Frank Kabel]

Hi
the worksheet_claculate event has a different procedure stub:
Private Sub Worksheet_Calculate()
'your code
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

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.

 

[Bob Phillips]

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)

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.