Worksheet Change Event

[GerbilGod7]

I have a dropdown menu (created with data validation) that refers to a
list of values in my sheet that can grow and shrink (the validation is
something like "=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)"

What I'm trying to do is to have the cell turn red when a certain value
is selected. To do this, I'm using the Worksheet_Change event. However,
the Change event does not seem to fire when I use the menu. I know the
routine works because if I change anything ELSE in the sheet, the event
fires and everything works like it's supposed to. It just seems to
ignore changes in the menu cell. It also ignores any related changes...
For example, if the menu is in B1, and C1 contains "=B1", selecting a
different value from the menu will change the value in C1 but won't
fire the event.

I can sort of get around this by changing Worksheet_Change to
Selection_Change, but it's uglier.

Any suggestions? Thanks!

 

[Ardus Petus]

Works by me!

Are you sure you check the right Target.Address ?

HTH

 

[Dave Peterson]

Are you using xl97?

From Debra Dalgleish's site:
http://contextures.com/xlDataVal08.html#Change

 

[GerbilGod7]

I'm pretty sure I am, but it doesn't even get that far... I put in a
notifier before the Target intersect to let me know when the change
event is called by the sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

MsgBox ("Change Event Called")

If Intersect(Target, Range("B1")) Then
...etc.

I am using '97 (at work), could that be the problem? I also created a
workbook from scratch with nothing in it but the list, the validated
menu referring to the list, and the change code. Same problem. However,
if I input a list directly into the validation form (i.e. if it's not a
dynamic list), the change event works.

Is this just an Excel (or an Excel 97) bug?

 

[GerbilGod7]

That answers the question, thanks!