count condition cell color

[streamer1234]

hello,

I'm a beginner to excel and I'm trying to get a formula to work,

=COUNTConditionColorCells(sheet, f#:f#, "color"), this is just an example. anyways i got the vba off youtube and it seems to work, only issue I've come across is i have about 14 sheets ( one for each month plus some info sheets), let say i use ^ the formula on January, I'm looking to count 4 colors, like I've said it works just fine once its all imputed but as soon as i go to another sheet ( ex: February) it copies exactly the same info from January.

please help lol, I'm starting to go insane. and saving it often doesn't save nothing it all changes constantly, if i open a sheet and correct the formula then go to the next sheet, it will automatically copy what was on the previous sheet, even if the formula is showing the correct range

photos
January is correct
February correct
March 1 is copping February
march 2 is the formula
march 3 is corrected


to help out a little this is the code i got from youtube

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


thank you

 

[Trebor76]

Hi steamer1234,

Welcome to MrExcel!!

See how this revised code goes:

Option Explicit
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 Long, CF2 As Long, CF3 As Long
    Dim condition As FormatCondition
    
    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
            On Error Resume Next
                Set condition = CFCELL.FormatConditions(1)
            On Error GoTo 0
            If Not condition Is Nothing Then
                '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
            End If
            Set condition = Nothing
        Next CFCELL
    Else
        COUNTConditionColorCells = "NO-COLOR"
        Exit Function
    End If
    
    COUNTConditionColorCells = CF2
    
End Function

Regards,

Robert

 

[streamer1234]

Good Morning Robert,

I've tried it out this morning but seems to be doing the exact same thing as before, still seems to grab the info from the other sheets

see attached from April and July, July has the exact #'s before its been corrected ( and what i do to correct them is i simply double click the cell and press enter and all the info come through properly )

any other suggestions? i know this is tricky and i appreciate all ideas!

thanks
A

 

[Trebor76]

and what i do to correct them is i simply double click the cell and press enter and all the info come through properly

Check your formulas are set to Automatic via the Calculations Option drop-down in the Calculation tab of the Formulas ribbon as it works for me

 

[streamer1234]

it was on automatic, switched it to manual, seems to be working

thank you!!
A

 

[Trebor76]

it was on automatic, switched it to manual, seems to be working

Hmmm...I would have thought it was the other way round. Leaving calculation on manual is not recommended as you may be basing assumptions of wrong figures as the formulas have not refreshed.

If you comment out Application.Volatile and set to Automatic does that do the trick?