Comparing Ranges

[Luis939]

I doubt this is possible, but is there a way to compare that a range of cells are identical in everywhere to another set of ranges such as
Range("A1:A3") and Range("B1:B3")
so i can spot any differences, even in formatting
i tried using the "=" but that didnt work, if not possible...what is the best way....test each cell individually? thanks!

 

[SkipVought]

Luis939 (MIS) May 12, 2004

dim a as range, b as range
for each a in Range("A1:A3")
  bSame = True
  for each b in Range("B1:B3")
    with a
      if .value <> b.value then bsame=false
      if .interior.color <> b.interior.color then bsame=false
...
    end with
  next
next

and on and on...



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Luis939]

hmm...do you know if in the help or somewhere there is a complete list of formatting properties, not just color? thanks!!!

 

[SkipVought]

Check out the properties of the range object in help or the Object Browser.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Zathras]

Hey Skip! Is it getting hot down there again? Why would you want to compare EVERY cell in range B with EACH cell in range A? (In this example, 9 comparisons instead of 3)

 

[SkipVought]

D*&% you're right! Actually, it's sweltering here. Humidity is high and the roof leaked yesterday making for it being steamy in here.

No excuse -- where's my head???



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Zathras]

I know just how you feel. Between vacation and special projects at work I've been so busy I haven't had time to do much here. Just looking around from time to time and butting in when I see something flagrantly wrong.

Keep up the good work!

 

[SkipVought]

Sounds like just plane WORK to me, sans GOOD!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Luis,

As Zathras has suggested, I put the HURT on you with my code.

dim a as range b as range, i as integer
set b = Range("B1:B3")
i = 0
for each a in Range("A1:A3")
  i = i + 1
  bSame = True
    with a
      if .value <> b(i).Value then bsame=false
      if .interior.color <> b(i).interior.color then bsame=false
...
    end with
next

Since both ranges share the same rows, the code could ALSO be written...

    with a
      if .value <> Cells(.Row, "B").Value then bsame=false
      if .interior.color <> Cells(.Row, "B").interior.color then bsame=false
...
    end with

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884