blonde118222
New Member
- Joined
- Feb 4, 2022
- Messages
- 1
- Office Version
- 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
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