Hiding Columns Based on Color

[Zach]

I'm working within a spreadsheet that had many many columns of data, based on the data certain columns are colored a light Yellow: RGB(255, 255, 102). Before I have to report the data I manually go through and hide all these columns, sometimes it can be large amount, so I'm trying to get a marco to just hide the columns based on the color. This is what I came up with but I'm not skilled enough to know what I'm missing.
I went ahead and created a template that color coded cells in row 4 as the columns I will need to hide each time(these will never change).

Dim cell As Range
  For Each cell In Range("4:4")
  If cell.Interior.ColorIndex = RGB(255, 255, 102) Then
  Columns("A:ZZ").EntireColumn.Hidden = True
  Exit Sub
End Sub

Can someone put me on the right path to fixing my mixed up Macro?

 

[Hui]

Zach
A few issues here

Your For and If loops weren't terminated
You need to check the Color not the Colorindex
Then Hide just the Column of the cell that you have checked not the whole range
Then Don't exit the sub until you have finished checking all the Columns
I would restrict the range of cells in Row 4 by defining a range rather than all row 4

Try:

  Dim cell As Range
  For Each cell In Range("A4:AA4") 'Change range to suit
  If cell.Interior.Color = RGB(255, 255, 102) Then
  Columns(cell.Column).EntireColumn.Hidden = True
  End If
  Next

 

[Zach]

Thanks Hui, that works perfect. Is there an easy way to write a reverse macro? That way I could hide or unhide all the columns as needed?

 

[SirJB7]

Hi, Zach!
Duplicate the same macro, change True by False. Or write one macro only with a parameter:

Option Explicit

Sub ToHideOrNotToHideThatIsTheQuestion(pbHide As Boolean)
    Dim cell As Range
    For Each cell In Range("A4:AA4") 'Change range to suit
        If cell.Interior.Color = RGB(255, 255, 102) Then
            Columns(cell.Column).EntireColumn.Hidden = pbHide
        End If
    Next
End Sub

And you should call it as follows, 1st for hiding, 2nd for unhiding:

ToHideOrNotToHideThatIsTheQuestion True
ToHideOrNotToHideThatIsTheQuestion False

Regards!

 

[Wim]

Hi ... this also helped me ... thanks!
I have a new problem though ... when I physically change the colour of a cell the code do exactly what it is suppose to do. I have now a spread sheet which change colour because of a set condition. On this the code does not work ... any ideas?

 

[jeremyjp]

Thanks @SirJB7 & @Hui That's great. I started with that and added a couple of tweaks b/c I hate keeping multiple copies of a macro updated.

Instead of triggering on a form button/activeX I trigger on a job and hide/unhide based on the fill color (forecolor.rgb) of the box, and it toggles depending on the current state. So it's one shared macro to call from any sheet for any color and the same button changes state.

@Wim I'd imagine you could append something in your macro that changes colors based on condition to add in changing the color of a targeted shape object.

Option Explicit
Sub show_hide_columns_color()
    Dim cell As Range
    Dim button_color As Long
    button_color = ActiveSheet.Shapes(Application.Caller).Fill.ForeColor.RGB
   
    For Each cell In Range("A1:CC1") 'Change range to suit
      If cell.Interior.color = button_color Then
            If Columns(cell.Column).EntireColumn.Hidden Then
                Columns(cell.Column).EntireColumn.Hidden = False
            Else
                Columns(cell.Column).EntireColumn.Hidden = True
            End If
        End If
    Next
End Sub