Color Format Visible Cells

  • 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.

    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

  • 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.


  • 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)

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

    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.

  • 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)


  • 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?

  • 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!