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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top