How to hide non-contiguous rows based on value of cell and the color of the rows

deZine

New Member
Joined
May 6, 2009
Messages
18
Hi,
I am trying to find an efficient way to list a set of non-contiguous rows to be hidden based on the value of a cell and the color of the rows being hidden. Right now I have this code that works but I would like the hiding to be based on the color of cell A in each potentially hidden row instead of having to list each row that needs to be hidden.

VBA Code:
If Target.Address(False, False) = "C16" Then
    Application.ScreenUpdating = False
    Rows("24:490").Hidden = False
    Select Case Target.Value
        Case "Level 1"
            Rows("28").Hidden = True
            Rows("100:189").Hidden = True
        Case "Level 2"
            Rows("24:85").Hidden = True
        Case "Level 3"
            Rows("24:336").Hidden = False
        Case "<Select>"
            Rows("24:459").Hidden = True
    End Select
    Application.ScreenUpdating = True
End If
End Sub

These are the list of colors:
#FABF8F - hidden for "Level 1"
#E26B0A - hidden for "Level 1" or "Level 2"

We would still like to hide rows 24:459 if "<Select>" is the value.

BTW - the list of rows will be much longer that what is being shown in the examples above.

Thanks in advance for your help!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
I'd recommend a for each loop based on the rows available and within the loop check for the color of the cell in A of the current row. If it matches the color for hiding then set that row hidden, otherwise leave it, or un hide it depending on your needs.
VBA Code:
Sub hideRows()
For Each c In Range("a1", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)).Cells
    If c.Interior.Color = 65535 Then  'set the color number you need here
        Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
End Sub
 

deZine

New Member
Joined
May 6, 2009
Messages
18
Thanks rondeondo!
That looks like it would be part of a perfect solution but I am too ignorant to carry it over the finish line on my own.
The colors that are hidden should be based on the text that is selected in cell C16 when the selection is made.
These are the list of colors:
#FABF8F - hidden for "Level 1"
#E26B0A - hidden for "Level 1" or "Level 2"

We would still like to hide rows 24:459 if "<Select>" is the value and unhide all if the "Level 3" is the value.

Thanks so much for your help!
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi @Devine
This code is really only for your option to hide cells by color, you could call it from your existing code.
In this version I've added an argument for the code so you can give it a level to base the color on.
So you call hideRows from your code using:
VBA Code:
hideRows(Target.Value)
VBA Code:
Sub hideRows(hideLevel AS String)
For Each c In Range("a1", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)).Cells

   If (hideLevel = "Level 2" and (c.Interior.Color = #FABF8F   OR c.Interior.Color = #E26B0A )) OR ( hideLevel = "Level 1" and c.Interior.Color = #FABF8F ) Then  
        Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
End Sub
Alternately the hideRows() argument could just be the color value, but there would need to be multiple arguments.
So then it could become:
VBA Code:
Sub hideRows(color1 AS String, optional color2 as string)
if color2 is nothing then

For Each c In Range("a1", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)).Cells
   If c.Interior.Color = color1 or c.Interior.Color = color2 Then  
        Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
End Sub
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156

ADVERTISEMENT

sorry I went back to edit this but the time available to edit ran out. ------ re-post
Hi @Devine
This code is really only for your option to hide cells by color, you could call it from your existing code.
In this version I've added an argument for the code so you can give it a level to base the color on.
So you call hideRows from your code using:
VBA Code:
hideRows(Target.Value)
VBA Code:
Sub hideRows(hideLevel AS String)
For Each c In Range("a1", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)).Cells

   If (hideLevel = "Level 2" and (c.Interior.Color = #FABF8F   OR c.Interior.Color = #E26B0A )) OR ( hideLevel = "Level 1" and c.Interior.Color = #FABF8F ) Then 
        Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
End Sub
Alternately the hideRows() argument could just be the color value, but there would need to be multiple arguments.
So then it could become:
VBA Code:
Sub hideRows(color1 AS String, optional color2 as string)
if color2 ="" then 'if you give it just one color then it doesn't break
  color2=color1
end if
For Each c In Range("a1", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)).Cells
   If c.Interior.Color = color1 or c.Interior.Color = color2 Then 
       Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
End Sub
So you'd call hideRows using:
VBA Code:
hideRows("#FABF8F")
or 
hideRows("#FABF8F","#E26B0A")
You might need to fiddle a bit with quotes or converting the color codes to decimal.
To see what color code is in use just put in
VBA Code:
msgbox c.Interior.Color
in the loop and it will give you the color being used in each cell
 

deZine

New Member
Joined
May 6, 2009
Messages
18
HI Rondeondo,
Thanks again for all the time and effort you put into this solution! Your code is working perfectly! Now I am just having some trouble hiding the checkboxes on the sheet but I will start a new thread for that.
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
good idea, you'll get a terse note if you keep adding questions to the one thread :)
Glad I could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,074
Messages
5,628,484
Members
416,321
Latest member
tomazik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top