I have a sheet with a number of instances of Union(Cell, VRange) to set formatting of cells depending on what has been input into them. I also have to periodically Copy/Paste Special/Values over these cells. This works fine except for one column where a msgbox is called after any cell in that column has been changed. I need a way of bringing up the msgbox if just that cell is selected, but skipping the cells in this column when the whole table has been selected for PasteSpecial/Values.

I have tried:

Set VRange = Range("Q6:Q899")
For Each Cell In Target
If Union(Cell, VRange).Address = VRange.Address Then

If Cell.Offset(0,-1).Selection <> ""

ans=msgbox("........

End If

End If
Next Cell


but it returns an error. Any help with setting this condition to choose if just the one cell or a larger part of the worksheet has been selected would be very much appreciated. I am sure there must be a very simple solution to this

Thanks in anticipation,
Paul