Define Range based on cell color

[Guest]

Is it possible to define a range based on cell color? What I'd like to do is
hide/unhide all columns containing cells with the background colors 34 and
37. After reading Dave Pearson's information, it seems possible but I have
no idea how to make it work.

 

[Frank Kabel]

Hi
one way:
- using Dave's information add a helper column showing the colorindex
- apply an Autofilter to show/hide the desired rows

 

[Tom Ogilvy]

Sub tester1()
Columns.Hidden = False
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 37 Or cell.Interior.ColorIndex = 34 Then
cell.EntireColumn.Hidden = True
End If
Next
End Sub

 

[Guest]

Thanks Tom.
It does hide the columns, but then appears to keep searching for additional
cells? I get the hour glass and it doesn't really stop searching. When I
hit Esc and Debug, the END IF statement is highlighted.

 

[Tom Ogilvy]

It is looking at every cell in the UsedRange (you didn't say where to look).
If you have a large UsedRange, then it may take a while. If there is a
smaller area it can search, then you can change it to search that

rather than
For Each cell In ActiveSheet.UsedRange

perhaps

For Each cell In ActiveSheet.Rows(1).Cells

for just the first row as an example.