Paul,
To make the code re-usable I was looking to be able to select two
ranges (or pass two named ranges) and for the macro to do the rest.
To determine the upper-left cell of the second range would be
rng2.cells(1,1), but the bit I was struggling with was determining
from rngCell exactly which cell - relative to the rng1 range that it
was working with.
I had another play with the code & think I might have now stumbled
upon a solution so will post here if it's useful for anyone else, but
if there is a better way of doing it then please do shout.
It uses rng1.Row & rng1.Column to determine the upper-left position of
the original range.
It also uses rngCell.Row & rngCell.Column to find the position of the
rngCell range.
By subtracting the starting row of rng1 from the row of rngCell - and
then adding 1 - you can determine the relative position of the rngCell
range within the rng1 range. Same principle applies to the column
position.
Dim rngCell as Range
Dim rng1 as Range
Dim rng2 as Range
Set rng1 = Range("rngOriginal")
Set rng2 = Range("rngComparison")
For Each rngCell In rng1
If rngCell.Value <> rng2.Cells((rngCell.Row - rng1.Row) + 1,
(rngCell.Column - rng1.Column) + 1).Value Then
MsgBox rngCell.Address & " - " & rngCell.Value
End If
Next
Rgds
Archie
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
