Excel VBA Hide Rows based on cell color and if command says "Yes"

Question

I am trying to hide rows based on two criteria:

Criteria 1: If cell Q3 has value "yes" hide cells that comply by criteria 2

Criteria 2: If cells in column A are the color RGB (253, 233, 217), hide that entire row.

Essentially, I have a list of days that tracks a count of emails per day and I want to hide any weekends so they don't show up on the graph that shows the trend. I'm dumbing it down my superiors so all they have to do is click "Yes" or "no" from a drop down in cell Q3 to hide the weekend rows. The weekends are colored a light orange (the rgb code listed above). it is also important that if the cell Q3 states "no" then all of the rows are to unhide/remain unhidden. The code I have right now is:

Sub HideRows()
BeginRow = 1
EndRow = 1000
ChkCol = 1
ChkCommCol = 17

For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCommCol).Value = "Yes" Then
        If Cells(RowCnt, ChkCol) = RGB(253, 233, 217) Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    If Cells(RowCnt, ChkCol).EntireRow.Hidden = True Then
        Cells(RowCnt, ChkCol).EntireRow.Unhide = True
    End If
Next RowCnt

End Sub

If you need more information, let me know! Thank you so much for your help.

Answer 1

you could try this code in any module code pane:

Sub HideRows()
    Dim beginRow As Long, endRow As Long, chkCol As Long, chkCommCol As Long, rowCnt As Long

    beginRow = 1
    endRow = cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A last not empty cell row index
    chkCol = 1
    chkCommCol = 17

    Rows.EntireRow.Hidden = False 'unhides all rows. Subsequent code will hide relevant ones 

    If cells(3, chkCommCol).Value = "Yes" Then '<--| if Q3 value is "Yes"
        For rowCnt = beginRow To endRow '<--| loop through the "limit" rows indexes 
            With cells(rowCnt, chkCol) '<--| reference current cell to be cheked
                .EntireRow.Hidden = (.Interior.Color = RGB(253, 233, 217)) '<--| set its corresponding Row 'Hidden' property to True if currently referenced cell has wanted color
            End With
        Next
    End If
End Sub

and place the following code in your relevant worksheet code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Q$3" Then HideRows '<--| if "Q3" cell has been changed then run 'HideRows' macro
End Sub