Counting Colour Cells whilst using a filter

blonde118222

New Member
Joined
Feb 4, 2022
Messages
1
Office Version
  1. 2021
Hi first post here and I'm hoping someone can help me.

I have a complex spreadsheet where I need to count the number of cells which have issued dates that go over a certain date from the date entered (note the spreadsheet also contains completed dates hence my need to include colours)

I've used the below VBA code to count colours

Function Color_Cell_Count(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range
Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then
Color_Cell_Count = Color_Cell_Count + 1

End If

Next Data_Range

End Function

however where im currently using conditional formatting to make the cell change colour when this happens it doesnt seem to count it properly. (works fine when i just colour the cell manually)

Also the count works fine until I apply a filter on the data is there a way I can add in a subtotal formula into this VBA or just get it to only count visible cells?

Any help would be greatly appreciated
Many thanks
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,898
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Have a look here: ExcelSirJi | VBA Code To Count Color Cells With Conditional Formatting

Note however that there may be an easier way to do this that does not require ANY VBA.
If your Conditional Formatting is set up with "rules", you can probably apply those exact same rules to a COUNTIF (or COUNTIFS) formula in order to get your count.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,255
Messages
5,852,917
Members
431,531
Latest member
jgottlieb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top