Excel: ActiveCell.Address returns next cell reference 2

[ShabanaHafiz]

I need to clear contents of few cells based on value change in one cell. I placed the following code in Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Address <> "$A$2" Then
    Exit Sub
Else
   ActiveSheet.Range("A8,A9,A10").ClearContents
End If

End Sub

This didn’t work. I placed a messagebox for debugging on the first line:
Msgbox ActiveCell.Address

It was found that Change event fired when I changed the value and pressed enter to move the highlighter to next cell (A3). So answer in message box was $A$3. How can I get the reference $A$2 to clear contents of A8,A9,A10 based on value change in A2.

 

[GlennUK]

You should be using Target.Address instead of ActiveCell.Address

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

The clue is in the sub title:

ByVal Target As Range) means that the sub is being passed a variable called "Target" - this refers to the range that was changed. If you think about it, there is logic there. Once a cell has been changed, the cursor moves so the ACTIVE cell is NOT the cell that has been changed....


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question