difficult conditional formatting 1

[andrew299]

hello all,
I have a problem with conditional formating that I need some help with. I have two workbooks with the same layout. I want to be able to compare the differences between them by highlighting any cells that are different by changing their colour.
The data is in the range ("b729")

I thought about using code similar to the following that I found in another thread but don't know how to read the data from another workbook.

Dim r As Long
Range("A:A").Font.ColorIndex = xlColorIndexAutomatic
r = 3
While Cells(r, "A") <> ""

If Cells(r, "A") = Cells(r - 1, "A") Then
Cells(r, "A").Font.ColorIndex = 3
End If
r = r + 1
Wend


Another complication is that I want to do this for all the sheets in the workbooks - they are named "1","2","3" etc
but his may vary each time I look at the workbook so I need something a bit more dynamic than a fixed for...next loop.
Also the cell reference needs to match the correct sheet each time.
Any ideas?
Thanks
Andrew



It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[SkipVought]

Hi,

dim wb1 as workbook, wb2 as workbookl, ws1 as worksheet, ws2 as worksheet, r1 as range
set wb1 = Thisworkbook
set wb2 = workbooks(2)  'whatever the other one is
for each ws1 in wb1.worksheets
  for each ws2 in wb2.worksheets
    if ws1.name = ws2.name then
      for each r1 in ws1.range("b7:o29")
        if r1.value = ws2.cells(r1.row, r1.column).value then
           'match
        else
           'no match
        end if
      next
    end if
  next
next

Skip,

_[red][/red]

 

[andrew299]

Thanks Skip,
Absolutely Perfect.
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)