Count Conditionally Formatted Cells (Visible Only)

Hi there! Macro beginner here.

I have a column where I count the number of conditionally cells formatted to be red as below:

Please Login or Register  to view this content.

All my data is laid out by # or % for each period. However, I have a button which will hide the % columns if you wish to only look at the numbers only. However, once the % columns are hidden, the macro is still counting the hidden conditionally formatted. How do I get it to only calculate visible cells?

Try something like this...

Please Login or Register  to view this content.

EDIT: Hm now seems to not be affecting it at all.

If you're just looking to count any visible cells that have conditional formatting,
try this example:

Please Login or Register  to view this content.

Is that something you can work with?

Double check for a typo. It shouldn't give that error unless it was entered incorrectly.

Otherwise, can post exactly your entire code. We may be talking past each other.

Originally Posted by AlphaFrog

Double check for a typo. It shouldn't give that error unless it was entered incorrectly.

Otherwise, can post exactly your entire code. We may be talking past each other.

I've updated my original post to include the entire code. Since adding it, it seems to not have made a difference.

Originally Posted by Ron Coderre

If you're just looking to count any visible cells that have conditional formatting,
try this example:

Please Login or Register  to view this content.

Is that something you can work with?

Hm do I just add that to the code? Sorry I'm very new.

Like I mentioned...it's just an example to play with.
Instead of starting with the UsedRange, use a row reference:

Please Login or Register  to view this content.

The message box is just to let you know what the result is

Ok...I understand. We all had to start somewhere when learning VBA.

Try this User Defined Function (UDF)

Please Login or Register  to view this content.

In your worksheet, if you wanted to count the number of visible cells with conditional formatting in H5:P5
you'd use that UDF this way:

Please Login or Register  to view this content.

Is that something you can work with?

I believe this code will work...as long as you are using Excel 2003 (it crashes on any version after 2003)

Please Login or Register  to view this content.

Does that help?

It doesn't appear to be working.. only giving me 0 values for every row now.

I wish I could test it...but, I haven't had Excel 2003 on a computer for several years.
Perhaps someone else could chime in?

Meanwhile:
Check if the cCell.DisplayFormat.Interior.Color = 255
• Select a CF cell with the red color
• ALT+F11....to see the VBA editor
• In the immediate window
...type this: ? ActiveCell.DisplayFormat.Interior.Color
...Press ENTER
What number displays?

I typed it right into one of my macros windows, which was what was open on the VBA editor.

Pressed enter, it now says:
Print ActiveCell.DisplayFormat.Interior.Color
and doesn't do anything else.

I selected the default red color that is available, it really should just be red (255,0,0).

If you don't see the Immediate Window
• View....select Immediate Window

Then
• Select one of the CF cells
• Enter this in that window (beginning with the question mark): ? ActiveCell.DisplayFormat.Interior.Color
• Press ENTER

What happens?

In that case, you'd need to use code that basically mimics the CF rule for each cell in the referenced range and makes a tally.
Seems like it might be an awful lot of work for minimal benefit. You might consider using a regular Excel formula to run the test(s) and return the count.