Jan 21 2022 03:05 AM
Jan 21 2022 03:39 AM
Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.
Use conditional formatting to highlight information
With your permission, if I may further recommend, if you follow a few small rules, you'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.
What I recommend: Always inform about the Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.) you use.
If possible and if necessary, add a file (without sensitive data) and use this file to describe your project step by step or add photos with the appropriate description.
Don't forget that not every Excel user has a clue about every job and that what you see he can't see.
In this link you will find some more information about it:
Welcome to your Excel discussion space!
Thank you for your understanding and patience
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Jan 21 2022 03:56 AM
Dear Christopher, I propose a small function to create in the vba module
Function color1(cellRef, Range)
Set reference = cellRef
numeroColor = reference.Interior.Color
Set myRange = Range
For Each sel In myRange.Cells
If sel.Interior.Color = numeroColor Then myColor = myColor + 1
Next
color1 = myColor
ActiveSheet.Calculate
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Regards
Patrice
Jan 21 2022 05:44 AM
If it absolutely must be with VBA, here is an additional example.
Example in the inserted Excel file.
Jan 21 2022 05:56 AM
Jan 21 2022 06:16 AM
Jan 21 2022 06:47 AM
Jan 21 2022 06:54 AM
@Patrice Brel I originally didn't test your macro, just looked at the code when I made those observations. @NikolinoDE makes a very good point. Basically only looking at .interior.color doesn't work for conditional formatted colors because the cell's interior color is still blank (or what ever color you set it to) but the conditional formatting overrides that color formatting.
that said Nikolino's code is summing the VALUES in the cells of those colors instead of counting them. i also had issues with the file format and not recognizing the functions correctly so here is Nikolino's file and macro with slight corrections but credit goes to Niko.
Jan 21 2022 06:58 AM