Hi,
May i ask how do I count the number of conditional formatted highlighted cells across the row
Please note each row has cells with different colour as different criteria highlight different colour. I need to know the number of cells highlighted with each colour across each row
Try VBA but cannot work
Tried this VBA also did not work
May i ask how do I count the number of conditional formatted highlighted cells across the row
Please note each row has cells with different colour as different criteria highlight different colour. I need to know the number of cells highlighted with each colour across each row
Try VBA but cannot work
PHP:
Function CountCFCells(rng As Range, C As Range)Dim i As Single, j As Long, k As LongDim chk As Boolean, Str1 As String, CFCELL As Rangechk = FalseFor i = 1 To rng.FormatConditions.Count If rng.FormatConditions(i).Interior.ColorIndex = C.Interior.ColorIndex Then chk = True Exit For End IfNext ij = 0k = 0If chk = True Then For Each CFCELL In rng Str1 = CFCELL.FormatConditions(i).Formula1 Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1) Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1)) If Evaluate(Str1) = True Then j = j + 1 k = k + 1 Next CFCELLElseCountCFCells = "Color not found"Exit FunctionEnd IfCountCFCells = jEnd FunctionTried this VBA also did not work
PHP:
Function CountCFCells(Rng As Range, ColorIndex As Long) As Long Dim I&, J&, Tmp$, Str1$ Dim CfCell As Range Dim FC As FormatCondition, IIFlg As Boolean For Each FC In Rng.FormatConditions If FC.Interior.ColorIndex = ColorIndex Then Exit For Next FC If FC Is Nothing Then Exit Function Str1 = FC.Formula1 For I = 1 To Len(Str1) Tmp = Mid(Str1, I, 1) If ("0123456789" Like "*" & Tmp & "*") Then IIFlg = True Else If IIFlg Then Exit For End If Next I Tmp = Right(Str1, Len(Str1) - I + 1) For Each CfCell In Rng Str1 = "=" & CfCell.Address & Tmp If Rng.Worksheet.Evaluate(Str1) = True Then J = J + 1 Next CfCell CountCFCells = JEnd Function
