Solved: Worksheet_change event with VLookUp

[lynnnow]

Hi,

I've got a table with values that will be inserted and a vlookup at the right end of the table based on these values. I've used the worksheet_change event to set colors on these inserted values. The problem I have is that if the value is inserted in the vlookup cell then the function works, but if the values are inserted in the proper column, then the worksheet_change event does not trigger.

The code i've got so far is as below:


If Target.Column = 19 And Target.Value = "Austin" Then
ActiveSheet.Range("C" & Target.Row, "C" & Target.Row).Interior.ColorIndex = 3

I've 29 such values in the vlookup that need to be color coded.

http://vbaexpress.com/forum/C:%5CDocuments%20and%20Settings%5C5869.GTLCMS%5CDesktop%5Cvlookup.gifAs you will see from the snapshot (sorry it is minute), Col C gets the inserted values and Col S is the vlookup. The code as mentioned above works only if the location (in the above case "Austin") is inserted manually in Col S, otherwise it doesn't work.

Any help is appreciated.

Thanks in advance.

Lynnnow

[Bob Phillips]

Use the worksheet_calculate event.

[lynnnow]

Hi,

I tried the Worksheet_Calculate event handler, but it now throws the error msg object required.

I guess it is searching for the place where in the sheet the calculation is occurring and it is stopping on the first line in the first If.

Please point my error.

Thanks

Lynnnow

[Bob Phillips]

You need to check every cell that you interested in, not target because there is no target.

[lynnnow]

Hi,

Could a little more direction be provided, as I'm new to the worksheet events.

Thanks

[Bob Phillips]

Something Like



For Each cell In Me.Range("S1:S100")
If cell.Value = "Austin" Then
Me.Range("C" & cell.Row).Interior.ColorIndex = 3
End If
Next cell


You need to correct the range of cells to the appropriate range

[lynnnow]

Tried it, but it still needs a little more tweaking.

[Bob Phillips]

And I am supposed to be able to work out what you mean by that?

[lynnnow]

Hey,

It isn't working and I will spend some time thinking on how to go about it tomorrow when I get back to work. There is no error message or anything, I don't even know if it is being triggered. For trial purposes, I used just a single location name ("Austin") but still no results. The range specified by you is wot i'm using. However, correct me if i'm wrong this section of code goes into the the Worksheet_calulate sub, right? No other reference to subs or functions?

Thanks

[lynnnow]

Hi,

GOT IT!!! :o: I put it in the workbook event instead of the worksheets. Should've seen it earlier. Guess was too tired from work to see the obvious. This is solved.