Thread: Count font color sum will not auto calculate

I have this formula to sum the cells with certain font colors that will not caluclate by itself unless under very few conditions:

=CountFontColor(A3:A13,-4105)-COUNTIF(A3:A13,"")

My problem is that I am creating this spreadsheet to be as easy to use as possible (users have little to no excel knowledge) and the cells that need to be counted are set to change color based on a conditional format - this conditional format is based on a range of cells in column B - which are drop-downs (to make it fool-proof for the user)

So, the problem is that column A is pre-populated with names, column B has a drop down....if a certain 'absence code' is selected in column B, the corresponding name in column A will change color....but the total of my formula =CountFontColor(A3:A13,-4105)-COUNTIF(A3:A13,"") remains the same until I click on the cell and hit 'enter'. I can't have these users doing that so how do I get it to calculate by itself?

Thanks

Hello Greed,

Since Excel 2003 and earlier does not have a native function to count font colors, this function must be a VBA macro, Yo can get it to recalculate when theever the worksheet is recalculated by adding the statement Application.Volatile to the beginning of the macro.

Example

Function CountFontColor(Rng As Range, ByVal FontColor As Long) As Long
  Application.Volatile

  'Other Code

End Function

Hi Leith,

Unfortunately, it doesn't seems to be working...not sure if I have done something wrong. Here is the code I have currently:

Function GetFontColorIndex(pRange As Variant) As Integer
    Application.Volatile
    
    Set pRange = pRange.Areas(1)

    GetFontColorIndex = pRange.Cells(1, 1).Font.ColorIndex

End Function

Function CountFontColor(pRange As Variant, pIndex As Integer) As Integer
    
    Application.Volatile

    Dim LTestRange As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    Dim LTotal As Integer

    Set pRange = pRange.Areas(1)

    LTotal = 0

    m = pRange.Rows.Count
    n = pRange.Columns.Count
    LTestRange = pRange.Value

    For i = 1 To m
        For j = 1 To n
            If pRange.Cells(i, j).Font.ColorIndex = pIndex Then
                LTotal = LTotal + 1
            End If
        Next j
    Next i

    CountFontColor = LTotal

End Function

If I select a drop-down in column B and it changes the color of the name in column A, the total remains the same.

Thanks -

Hello Greed,

VBA can not count cell colors if they are set by Conditional Formatting.

Hello greed,

You can use the worksheet function CountIf to return the count of cells in a range that match a given value.

To count the blank cells

Sub CellCount()
  
  Dim CellCnt As Long
  Dim Rng As Range
  
    Set Rng = ActiveSheet.Range("A1:A100")
    
    CellCnt = WorksheetFunction.CountIf(Rng, "AAAA")
    CellCnt =CellCnt + WorksheetFunction.CountIf(Rng, "")
    MsgBox "Cell count is " & CellCnt

End Sub

Hello greed,

Glad to help.

Hello Greed,

In that case, you will need to a workbook for me.

Hello Greed,

You just needed a couple of array formulas to do the job. Here is the first formula.

=SUM(--(COUNTIF(B3:B13, G23:G28)))
Enter using Ctrl+Shift+Enter

Because your total cell is part of a merged cell, the cells must be unmerged before adding the formula. The formulas have been added to the attached workbook.