Color Format Visible Cells

Michael Avidan · Jan 15th 2006

Hi,

In he attached file I tried to color format visible cells as a result of an Auto Filter.

I managed to color the visible cells BUT upon filtering again I thought I should clear the color format from the previous filtered cells.

As you will see from the code I tried to begin with clearing column A but it seems that the invisible cells (the hidden) are not cleared.

Example:
If you filter by A and run the macro 4 "A" cells will get a green background.
If you will, now, filter by "B" - the "B" cells will now get their green background, BUT the "A" cells remain green which is incorrect.

Will you be so kind to correct/alter my code.

Thanks, Michael

Dave Hawley · Jan 16th 2006

Re: Color Format Visible Cells

Hi Michael

How about a custom function that Displays AutoFilter Criteria

Michael Avidan · Jan 16th 2006

Re: Color Format Visible Cells

Thanks Dave,

1. I tried to define the example just to learn what it means BUT I could not figure out the use of it.
(althogh I followed the instructions I got only #value errors etc...)

2. I could not figure out how this will resolve my problem/

I will be grateful if you could attach some file with a working AutoFilter_Criteria Function and also that the filtered fields will be colored.
(The color should remain also upon canceling the filter (showing all fields) and until some other filter is used.

Michael

Insomniac · Jan 17th 2006

Re: Color Format Visible Cells

Hello, 2 ways I can suggest:

Both these ways using the Calculation event to make it automatic, need a formula on the sheet to force it to fire.
I changed your header in A1 to:

="AUTO FILTER "&SUBTOTAL(3,A2:A1000)

Then first way: (Add code to the SheetCode Module)

Code

Private Sub Worksheet_Calculate()
Static r As Range, s As Range
    
    On Error Resume Next ' in case no specialcells
    With Me
        If .FilterMode Then
            With .AutoFilter.Range.Columns(1)
                Set r = .Offset(1).Resize(.Rows.Count - 1)
                Set s = r.SpecialCells(xlCellTypeVisible)
            End With
        End If
        If Not r Is Nothing Then
            r.Interior.ColorIndex = xlNone
            s.Interior.ColorIndex = 35
        End If
    End With


End Sub

Display More

2nd way using some code and Conditional Formatting:
Code for Sheet Module:

Code

Private Sub Worksheet_Calculate()
Dim crit As String


    With Me
        If .FilterMode Then
            Application.EnableEvents = False
            crit = .AutoFilter.Filters(1).Criteria1
            .Names.Add "Filt1", Right(crit, Len(crit) - 1)
            Application.EnableEvents = True
        End If
    End With


End Sub

Display More

Then use Conditional format for column A:
Cell Value Is >> equal to >> =Filt1
(and give it a format)

Remember both these codes go in the Sheet module & you need to have a formula in the sheet to make them work.

Michael Avidan · Jan 17th 2006

Re: Color Format Visible Cells

Thanks a lot.

I only checked suggestion #2.
It seems to be easier to implement.

However, one last question:
It takes a few seconds for the Conditional Format to color the filtered cells.

Is this normal or can be faster !?
(my computer uses a P-4 2.6 CPU and has 512 MB of RAM)

Michael

Insomniac · Jan 17th 2006

Re: Color Format Visible Cells

It works almost instantly for me on your sample.

I only using a PIII 800mhz + xl97 for testing.

Try the 1st code & compare.

Have you got a lost of other fomulas/conditionalformats on your sheet?

Possible setting calculation to manual at the start of code & back to auto would maybe help?

Michael Avidan · Jan 17th 2006

Re: Color Format Visible Cells

Everything O.K. now.

I don't know what caused the delay.

I checked both events and they work perfect.

Again, thanks for all your effort.

Michael

Color Format Visible Cells

Participate now!

Share