I've not known the Find method would fail when strings have a length
more than 256 until now. I think there seems to be no way to fix this
and your posted code is a way to solve this problem. I modified your code.

Sub CompareRangeDemo()
Dim oldupdate1 As Range, oldCell As Range
Dim newupdate1 As Range, newCell As Range

Set oldupdate1 = Range("b53:b86")
Set newupdate1 = Range("l53:l86")

oldupdate1.Interior.ColorIndex = xlColorIndexNone

For Each oldCell In oldupdate1
Found = False
For Each newCell In newupdate1
If oldCell.Value = newCell.Value Then
If Cells(oldCell.Row, "G") = Cells(newCell.Row, "Q") And _
Cells(oldCell.Row, "H") = Cells(newCell.Row, "R") Then
Found = True
Exit For
End If
End If
Next
If Not Found Then
oldCell.Interior.ColorIndex = 6
End If
Next

End Sub

Keiji

Anders wrote:
Hi Keiji,

I ran into one problem, if a cell has 256+ characters, it spits an error 13
mismatch. I get why - but is it possible for me to check the document and
alert the user which cell has over 256 characters and to fix?

Anders


"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

Hi Anders

I'm not sure this would satisfy your requirement, but try this one.

Sub CompareRangeTest()
Dim oldupdate1 As Range
Dim newupdate1 As Range
Dim CoR As Range
Dim FtAddress As String

Set oldupdate1 = Range("b53:b86")
Set newupdate1 = Range("l53:l86")

oldupdate1.Interior.ColorIndex = xlColorIndexNone

For Each oldCell In oldupdate1
Set CoR = newupdate1.Find(oldCell, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not CoR Is Nothing Then
FtAddress = CoR.Address
Do
If Not (Cells(oldCell.Row, "G") = Cells(CoR.Row, "Q") And _
Cells(oldCell.Row, "H") = Cells(CoR.Row, "R")) Then
oldCell.Interior.ColorIndex = 6
End If

Set CoR = newupdate1.FindNext(CoR)

Loop While Not CoR Is Nothing And CoR.Address < FtAddress
Else
oldCell.Interior.ColorIndex = 6
End If
Next

End Sub

Keiji

Anders wrote:
Hi Keiji,

I went a different direction from hiding as I found it helpful to see all of
the data, even that which hadn't changed - but I need help again. Here's
what I'm doing.

I'm comparing a range in b to a range in l, and if a cell in the b range
doesn't exist in l, then it highlights it in b. I also reverse it to
highlight any new data in l. I'm comparing update reports from two periods
and need to see the changes.

Dim oldupdate1 As Range
Dim newupdate1 As Range
Dim Found As Boolean
Set oldupdate1 = Range("b53:b86")
Set newupdate1 = Range("l53:l86")
For Each oldCell In oldupdate1
For Each newCell In newupdate1
If oldCell.Value = newCell.Value Then Found = True
Next
If Found = False Then
oldCell.Interior.ColorIndex = 6
End If
Found = False
Next

This works great. However, it falls a bit short. In the sheet, there is
text in "b and l", a date in "g and q", and text in "h and r" that all relate
(project goal, expected completion date, status - b53+g53+h53 are all
related). To compare, I'd really like to compare the group as a whole
"b,g,h" to any grouping in "l,q,r" I would like to highlight if any cell in
that group has changed.

When I reset the range to (b53:h86) and (l53:r86)The 'for each cell' won't
work because the dates and text in g/q and h/r are repeated multiple times so
they will never be highlighted. Is there a way to set for each row, b/g/h
and l/r/q as unique sets to compare? I guess i could concatenate the three
into a dummy column, compare and hide the dummy columns and then highlight
the orignal data cells? Is there a more efficient way?

TIA

Anders

"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

Hi Anders

If you don't mind, post your coloring code. if you could successfully
color the cells or rows, I think it's not so difficult to hide the rows.

Keiji

Anders wrote:
Hi Keiji,

I couldn't get that to work right.

I gave up on hiding the rows, and stuck with the coloring if <. It works
well, just more document to comb through.

Jacob, Keiji and Rick. Thanks for the help.



"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

This code seems to have redundant part, but give it try.

Sub Compare2ShtsTest()
Dim shPrimary As Worksheet
Dim shSecondary As Worksheet
Dim rRangePrimary As Range
Dim rRangeSecondary As Range
Dim strPrompt As String
Dim I As Long

Set rRangePrimary = Range("G1:H536")
Set rRangeSecondary = Range("Q1:R536")
Set shPrimary = rRangePrimary.Parent
Set shSecondary = rRangeSecondary.Parent

With rRangeSecondary
For I = 1 To .Rows.Count
Select Case I
Case 1, 39, 78

Case Else
If shSecondary.Cells(I, "G") = .Cells(I, "Q") _
And shSecondary.Cells(I, "H") = .Cells(I, "R") Then
.Rows(I).Hidden = True
Else
.Rows(I).Hidden = False
End If
End Select
Next I
End With

End Sub

Keiji

Anders wrote:
Jacob and Rick

As I look at my original code - I'm not sure it's doing what I want it to.
It seems to be skipping some cells and killing some I want to see. Maybe you
have something better?

Recap - for a set range (rows 2-38, 40-77 etc (rows 1,39,78 are headers I
would like to exclude from the process if possible)) IF cells G and H match Q
and R, I want to hide them. This leaves all of the exceptions visible. If I
can leave the headers, it tells me the file reference to where the exception
is found. BTW, the data in G and Q are dates, and H and R are text.

Eternally grateful.
Anders

"Jacob Skaria" wrote:

Do you mean...

For i = 1 To .Rows.Count
If .Cells(i).Value < rRangePrimary.Cells(i).Value Then Rows(i).hidden = true
Next i

If this post helps click Yes
---------------
Jacob Skaria


"Anders" wrote:

Hi All,

I have this sub (below) working if I change the
.cells(i) to cells(i).Interior.ColorIndex = 3
but I don't want to color it red if <, I want to hide it. cells(i).hidden
= true doesn't work.

Any help is greatly appreciated!

TIA,
Anders


Sub Compare2Shts()

Dim rRangePrimary As Range
Dim rRangeSecondary As Range
Dim strPrompt As String

Set rRangePrimary = Range("g1:h536")
Set rRangeSecondary = Range("q1:r536")

With rRangeSecondary
For i = 1 To .Rows.Count
If .Cells(i).Value < rRangePrimary.Cells(i).Value Then
.Cells(i).Hidden = True
End If
Next i
End With

End Sub