Create a Count of Coloured cells in Conditionally formatted sheets
Count the number of each colour Of cells. Use a function to count colours. The condition created by using a formula for the colour. Check out my online courses www.easyexcelanswers.com/courses.html
All my courses include online support and a user manual
Let me teach you the VBA that I have learn in my five years of consulting
www.easyexcela...
Let's take the frustration out of user forms
www.easyexcela...
Become an Affiliate and earn 25% on Course Sales
barb-s-school-...
Code
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
'make the worksheet always update
Application.Volatile
'define my variables
Dim Work As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Work = False
'for the first conditional format to the number of conditions in the range
For CF1 = 1 To CellsRange.FormatConditions.Count
'if the first condition colour is in the range then start counting
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Work = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Work = True Then
For Each CFCELL In CellsRange
'count the colours in the range
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function
For more help visit my website www.easyexcelan... or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
Check out my next one-hour Excel Webinar
www.crowdcast....
I use a Blue condensor Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.c...
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
19 авг 2024