UDF Count the number of cells coloured by conditional formatting

Claire Jackson

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
I've been searching for days on this and finally have had to ask for help. I can see its been asked many times before but each example I find doesn't work.

I have a spreadsheet with part of it conditionally formatted with orange based on certain criteria. All I want to do is count the amount of coloured cells and I just can't get it to work.

The cell colour I am looking for is in cell BP1 and the formula in BP2 is this: =@CountConditionColorCells($K$13:$BN$22,BP1)

the code I have is this:

VBA Code:
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
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
 

Attachments

  • Resource.jpg
    Resource.jpg
    161.4 KB · Views: 61
Last edited by a moderator:
I did want to continue on to address the UDF issue.
@GWteB unfortunately DisplayFormat does not work in a UDF.
The source for this is:
Range.DisplayFormat property (Excel)
I know UDF's have limitations in changing colors etc.. I didn't know you couldn't determine colors either.
There is a workaround for this posted in the Forum by @Jaafar Tribak on 30 Nov 2020 here:-
Using DisplayFormat in a UDF

The below is based on the original UDF posted in that it needs the Range evaluate in terms of color and a cell that has the desired color as a reference.
You could change this and hard code the color.
I have hard coded D2 as the Trade reference cell and Column B to evaluate Trade.
Jaafar's code is at the bottom.

VBA Code:
Function COUNTConditionColorCellsByTrade(CellsRange As Range, ColorRng As Range)

    Dim findColor As Variant
    Dim rCell As Range
    Dim cntMatch As Long
    Dim cntCells As Long
    Dim tradeCell As Range
    
    findColor = ColorRng.Interior.Color
    cntMatch = 0
    cntCells = 0
    Set tradeCell = Range("D2")
    
    For Each rCell In CellsRange

        Dim rCellColor As Variant
        rCellColor = DFColor(rCell)

        If rCellColor = findColor And Cells(rCell.Row, 2).Value = tradeCell.Value Then
            cntMatch = cntMatch + 1
        End If
        
        cntCells = cntCells + 1
            
    Next rCell

    COUNTConditionColorCellsByTrade = cntMatch
    
End Function

'https://www.mrexcel.com/board/threads/using-displayformat-in-a-udf.1154593/
'30 Nov 2020 Jaafar Tribak
Function DFColor(ByVal R As Range) As Double
    Application.Volatile
    DFColor = Evaluate("Helper(" & R.Address() & ")")
End Function

Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function
 
Upvote 0

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,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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