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.

If your code isn't working, please describe any errors and where they occur. — SJR
– SJR, Commented Mar 6, 2017 at 13:46

Jordan · Accepted Answer · 2017-03-06 14:12:13Z

First of all, you're missing a few End If statements throughout your code, I've edited your question with some indentation to help show where these are, try to indent in the future to help ensure you close off Loops and If statements.

With regards to your If statements, you need to check the cells' interiors for their colours as opposed to just the cells. This is done with the syntax:

Cells(x, y).Interior.Color = RGB(r, g, b)

Try this:

Sub HideRows() BeginRow = 1 EndRow = 1000 ChkCol = 1 ChkCommCol = 17 Application.ScreenUpdating = False 'Speeds up subroutine Application.Calculation = xlCalculationManual If Cells(3, ChkCommCol).Value = "Yes" Then 'This line checks that `Q3` is "Yes" For RowCnt = BeginRow To EndRow 'This line loops through the rows and hides the weekends If Cells(RowCnt, ChkCol).Interior.Color = RGB(253, 233, 217) Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt Else Rows.EntireRow.Hidden = False 'This line unhides all rows in the activesheet if `Q3` isn't "Yes" End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Thank you for this advice, I will definitely work to keep my lines properly indented. I ran the code and unfortunately, nothing happened. I used the top portion (beginrow, endrow, etc.) from something I found online but I don't know if that is the proper notation for what I want to do. There are no bugs with the code, it just doesn't change anything in the excel sheet.
Okay, looking again there are a few issues, the cell you are actually checking for Yes/No is actually being incremented from BeginRow to EndRow. I'll edit the code now as I'm sure we can cut it down a bit anyway.
Edited now, just to confirm, the column you are checking the cell colour for is currently the value of ChkCol which is column A, is that correct?

user3598756 · Accepted Answer · 2017-03-06 15:08:37Z

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

I would love to tell you if this code worked out; however, when I entered it into the module and pressed Run, it started but now it won't stop. VBE is constantly running and the excel book itself is constantly saying Calculate in the bottom left corner. I can't select or edit any cells while this is happening. I ran this while Q3 said "Yes" but I don't know if that caused this.
Sorry, I erroneously type Worksheet_SelectionChange instead of Worksheet_Change(). And missed a .Row. See edited code. let me know
No worries, I wanted to make sure that it wasn't something that I had some wrong. I added the changes and it still only hides one row and I still have to manually run the macro. Any ideas why this isn't changing when I changed the code?
you have to follow the "instructions" I gave you: the 2nd code must be put in your relevant worksheet code pane (not in a standard module) and have each changing of its Q3 cell trigger the macro. Step through your code to see it's really like that
You can add Application.ScreenUpdating=False at the beginning if the sub and Application.ScreenUpdating=True right before its end.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

8 Comments

Related