Change Conditional Formatting Rules based on User Input (vbyes/vbno) Response|VBA & Macros|Excel Forum|My Online Training Hub

Hello,

I am wondering if it is possible to change how a table is conditionally formatted based on a user input response (vbyes or vbno)?

In the project I'm working on, when the user clicks on the Query button, they are then prompted to answer yes or no to the question: "Is this query family related?". If they select "Yes", I would like to find, highlight, and sort to the top all the rows that match the Brand and Size given in J5 and J7, which are populated automatically depending on the product selected from the dropdown in J4. 

If they select "No", I would like to find, highlight, and sort to the top only the exact product selected from the dropdown in J4. 

Currently, when the macro runs, it only finds, highlights, and sorts to the top the exact product, regardless whether the user chooses "Yes" or "No". 

The coding I have thus far is below and the file is attached. 

Sub SelectedItem()
'
' SelectedItem Macro

answer = MsgBox("Is this query product family related?", vbYesNo)
If answer = vbYes Then
'affect totals for family - bring all products in the family to the top (highlight)
MsgBox "Great! Calculating query for all products within this family."

ActiveWorkbook.Worksheets("Family Related").ListObjects("FamilyData").Sort. _
SortFields.Add(Range("FamilyData[[#All],[Size]]"), xlSortOnCellColor, _
xlAscending, , xlSortTextAsNumbers).SortOnValue.Color = RGB(255, 230, 153)

With ActiveWorkbook.Worksheets("Family Related").ListObjects("FamilyData").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWorkbook.Worksheets("Family Related").ListObjects("FamilyData").Range.AutoFilter Field:=4, Criteria1 _
:=RGB(255, 230, 153), Operator:=xlFilterCellColor

With Sheets("Family Related")
'.Cells.EntireColumn.Hidden = False
.Columns("A").Hidden = True
.Columns("B").Hidden = True
.Columns("C").Hidden = True
.Columns("D").Hidden = True
.Columns("E").Hidden = True
End With

'Selection.EntireColumn.Hidden = True

Worksheets("Family Related").ListObjects("FamilyData").Range.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Item Run Info").Range("AG16")

ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort. _
SortFields.Add(Range("ItemRunData[[#All],[Item]]"), xlSortOnCellColor, _
xlAscending, , xlSortTextAsNumbers).SortOnValue.Color = RGB(255, 230, 153)
With ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWindow.Zoom = 70

ElseIf answer = vbNo Then
'Run below code and calculate as normal
MsgBox "Ok! Calculating query for selected product."
ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort. _
SortFields.Add(Range("ItemRunData[[#All],[Item]]"), xlSortOnCellColor, _
xlAscending, , xlSortTextAsNumbers).SortOnValue.Color = RGB(255, 230, 153)
With ActiveWorkbook.Worksheets("Item Run Info").ListObjects("ItemRunData").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End If

End Sub

Any help/advice is greatly appreciated!

Thank you,

Amanda