Hide columns based on header row color?

[RogerC]

I often need to hide the same columns in my Workbooks just for the purpose of printing them. To make them easy to recognize, I have the columns that I hide formatted blue in the header row and the ones I print are yellow. Is it possible to make a macro that would recognize the blue cells in row 1 and hide those columns? My right mouse button is getting worn out!

 

[phantom1975]

FINALLY SOMEONE ELSE FROM ARIZONA!!!

This macro will do the trick as long as you select a cell that has the color you wish to hide:<pre>Private Sub CommandButton1_Click()
Dim ThisCell As Range
Dim ThisColor As Long
ThisColor = ActiveCell.Interior.Color
For Each ThisCell In Range("A1:IV1")
If ThisCell.Interior.Color = ThisColor Then
ThisCell.EntireColumn.Select
Selection.EntireColumn.Hidden = True
End If
Next ThisCell
End Sub</pre>

_________________<MARQUEE>***************** EXCEL/VB NEWBIES ARE MY <font color="red"> FAVORITE</font>! ****************</MARQUEE>
This message was edited by phantom1975 on 2003-02-06 15:08

 

[Mudface]

Try something like the below. You could also use this in the Workbook BeforePrint event, with a line to check the sheet name before hand and exit the sub if the active sheet doesn't require columns to be hidden. Record a macro of you unhiding all the columns on a worksheet to reverse the process.
<pre>
Public Sub HideColumns()
Dim rngColoured As Range
Dim c As Range

Set rngColoured = Range("A1", Range("IV1").End(xlToLeft))
For Each c In rngColoured
With c
If .Interior.ColorIndex = 41 Then
.EntireColumn.Hidden = True
End If
End With
Next c

End Sub
</pre>

 

[RogerC]

Mudface - I modified your code slightly and placed it in the Sheet Object...

Sub CommandButton1_Click()
Dim rngColoured As Range

Dim c As Range



Set rngColoured = Range("A1", Range("IV1").End(xlToLeft))

For Each c In rngColoured

With c

If .Interior.ColorIndex = 37 Then

.EntireColumn.Hidden = True

End If

End With

Next c



End Sub

When I run it in Excel2002 it works perfectly. However when I run the same workbook using Excel97 I get an 'Unable to Set the Hidden Property of the Range Class' error on the .EntireColumn.Hidden = True
line. Can you tell me what I'm doing wrong?