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.

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

--
Regards
Frank Kabel
Frankfurt, Germany

StephanieH wrote:
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.

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

--
Regards,
Tom Ogilvy




"StephanieH" wrote in message
...
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.

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" wrote:

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

--
Regards,
Tom Ogilvy




"StephanieH" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy

"StephanieH" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy




"StephanieH" wrote in message
...
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.

Awesome. You're right, it was searching through the whole sheet. I went in
an deleted all unused columns/rows so hopefully it won't recognize that space
as "used".

Specifying a row made all the difference.

Thanks again..

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"StephanieH" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy




"StephanieH" wrote in message
...
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.

Once you delete entirerows and columns, then you need to save the workbook
for the UsedRange to be "recalculated"

Debra Dalgleish has instructions:
http://www.contextures.com/xlfaqApp.html#Unused

--
Regards,
Tom Ogilvy



"StephanieH" wrote in message
...
Awesome. You're right, it was searching through the whole sheet. I went
in
an deleted all unused columns/rows so hopefully it won't recognize that
space
as "used".

Specifying a row made all the difference.

Thanks again..

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"StephanieH" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy




"StephanieH" wrote in message
...
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.