<> then hide

  • Thread starter Thread starter Anders
  • Start date Start date
A

Anders

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
 
You can't hide the range of cells G1:H536... you must hide entire columns
(or rows). Is that what you want to do?
 
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
 
Hi Rick,

Trying to hide that entire row if those 4 cells match. If they don't, I
want to keep the row visible.

Thanks,
Anders
 
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
 
Anders

Now the requirement is a bit more clearer. Comments included below for
better understanding...Try and feedback

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

'Check whether G or Q has a date (to ignore headers)
If IsDate(.Cells(i).Value) = True Or _
IsDate(rRangePrimary.Cells(i).Value) = True Then

'If cells G and H match Q and R, I want to hide them
If .Cells(i).Value = rRangePrimary.Cells(i).Value And _
.Cells(i, 2).Value = rRangePrimary.Cells(i, 2).Value Then _
Rows(i).Hidden = True

End If

Next i
End With

End Sub

If this post helps click Yes
 
Hi Jacob,

I'm having some issues with the code. To test it, if I put the same date in
g and q (with h,r blank), in row 1, it works. If I move to row 2, with
nothing or text (xx in both) in row one, it hides row 3. If I put a non
matching date in row one, it also hides row three.

I'm confused and have no idea why it won't work. The hiding of row three is
really throwing me off.

FYI,
The data I'm running this through is structured like this.
A compilation of 15 source sheets, merged into one document. rows g and q
are originally calendar control 11.0, and h and r are data validation cells
from a list.


row 1: header for source (don't want to hide) but is blank as data is in A1,
but centered across selection
row 2-3: text (can hide - but is in cell a* but also centered across
selection)
row 4: text in cell g or q which is a header for the column, will be the
same and could be hidden.
row 5-37: rows g and q are formatted as dates (actually if I click format
cell, says custom and then mm/dd/yyyy.)
Then rows 1:37 are repeated, 15 times. I'm cool putting in the work to
specify each range (especially as this expands to 25+ in the future).

Thanks,
Anders
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
Back
Top