CODEWITHSUNDEEP
excel
Narek Hiulazian
Narek Hiulazian

Reputation: 13

vlookup function doesn't work with cell change handler

I use Excel. I have two files and '=vlookup' function that imports values from another file.

I have set cell change handler like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [b1]) Is Nothing Then
        [c1].Interior.ColorIndex = 3
    End If
End Sub

So I want to see it orange colored if value of 'vlookup' cell changes from another file. But it doesn't work.

P.S. If I use on cell change for manual cell change, it works. But doesn't work with 'vlookup'.

Upvotes: 1

Views: 180

Answers (1)

xwhitelight
xwhitelight

Reputation: 1579

The Worksheet_Change() event occurs when you manually change cells' formula or value. When the other file changes and causes the vlookup cell changes, that's not because you change it (you don't have to type anything) but it's recalculated.

Try this:

Private Sub Worksheet_Calculate() 
    [c1].Interior.ColorIndex = 3
End Sub

Upvotes: 1

Related Questions