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

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

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

"Adam" schrieb im Newsbeitra
..
Hi
I use the following to change the background color of a cell. I
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
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 Su

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Adam" schrieb im Newsbeitrag
...
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

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