Hello, What I am looking for is to have a Message box pop when and ask "What Categories?". and then if you type in either "Condition or Testable" (Those are 2 Table titles). After doing so I would like for it to hide any rows equal to the criteria in 1 of the 2 based on the answer given. I am having trouble with the code I have Currently. I used a code on a thread that somewhat answered my question but instead giving me what I want I can only type in a category name and it will only keep that item. I don't know where to edit the code at. Could someone please help. Here is my whole code below. PivotTable2 is in column A and B. The other 2 tables are in columnd F2:F217(Condition) and H2:229(Testable). Also the table criteria will change but that will be every now and then. Thank you
Code:
Sub colorAndHide()
Dim myCell As Variant
Dim cell As Variant
Dim ws As Worksheet
Dim rng As Range
Dim c As Variant
Dim p As Variant
Dim lastrow As Integer
Dim myColor As Byte
Dim firstaddress As Variant
Dim myPT As String
Dim myField As String
Dim Msg, Style, Title, ans
'=====================================================
Set ws = Worksheets("Cats")
lastrow = ws.Range("A65536").End(xlUp).Row
Set rng = ws.Range("A2:A" & lastrow)
myColor = 36 'pale yellow
myPT = "PivotTable2" ' Name of the Pivot Table
myField = "CategoryName" ' Pivot Table field
'=====================================================
rng.Interior.ColorIndex = xlNone
' Make all rows in Pivot Table visible
On Error Resume Next
For Each p In ws.PivotTables _
(myPT).PivotFields(myField).PivotItems
p.Visible = True
Next p
On Error GoTo 0
TryAgain:
myCell = InputBox("What Categories?", "List Name")
With rng
Set c = .Find(myCell, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = myColor
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
Else
Msg = "Category Name" & myCell & "Condition?" & vbNewLine & _
"Try Again?"
Title = "OKay"
Style = vbYesNo + vbExclamation
ans = MsgBox(Msg, Style, Title)
If ans = vbYes Then GoTo TryAgain
Exit Sub
End If
End With
If MsgBox("Do you wish to hide rows?", vbYesNo) = vbNo Then
rng.Interior.ColorIndex = xlNone
Exit Sub
End If
' If yes:
On Error Resume Next
For Each cell In rng
If cell.Interior.ColorIndex = myColor Then
myCell = cell.Value
Exit For
End If
Next
On Error GoTo 0
' Hide rows in Pivot Table
For Each p In ws.PivotTables _
(myPT).PivotFields(myField).PivotItems
p.Visible = (p.Name = myCell)
Next p
Set ws = Nothing
Set rng = Nothing
End Sub
Last edited by a moderator:
