How to store a cell address to variable VBA

Question

I am writing a simple macro to compare a modified value (on one sheet) to its original value (on another sheet) after a change is made. If they are different values, I want the both of these cells to be given a yellow fill.

However, I have been getting a type mismatch error when trying to store the address of the target cell. As I understand it, Target.Address returns a string. Setting Location = Target gives location the value of the target, not its address. How can I reference the same address of the target on the original sheet when changes are made?

Dim Location as Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set Location = Target.Address

If Target.Value = Sheets("Original").Range("Location").Value Then
   Target.Interior.Pattern = xlNone
   Sheets("Original").Range("Location").Interior.Pattern = xlNone

Else
   Target.Interior.Color = 255
   Sheets("Original").Range("Location").Interior.Color = 65535

End If

End Sub

Thanks for any help!

Possible duplicate of [What is the difference between dim and set in vba](http://stackoverflow.com/q/3872339/11683) — GSerg, Sep 05 '16 at 19:25
Although it would appear you want conditional formatting instead. — GSerg, Sep 05 '16 at 19:27

score 4 · Accepted Answer · answered Sep 05 '16 at 19:26

Dim Location as range

Private Sub Worksheet_Change(ByVal Target As Range)

Set Location =Sheets("Original").Range(Target.Address)

If Target.Value = Location.Value Then  
    Target.Interior.Pattern = xlNone
    Location.Interior.Pattern = xlNone   
Else   
    Target.Interior.Color = 255         
    Location.Interior.Color = 65535
End If

End Sub

Note: this will raise an error if Target is a multi-cell range, because you can't compare Value across the two ranges unless it's a single cell.

How to store a cell address to variable VBA

1 Answers1