To count highlighted cells, the following code works for me
Once the code has been added to a module, each function can either be used as a regular Excel function in a spreadsheet cell or as a function in other VBA code.
FIRST FUNCTION:
Function CountHighlightedCells(rngCells As Range, lColorNumber As Long, Optional bAnyColor As Boolean = False) As Long
'Counts the number of cells in the selected range that are highlighted with the color specified using
'lColorNumber. Use GetColorNumber function to retrieve the color number for a specified cell.
'If bAnyColor is set to True, this function will count any cell that is highlighted in any color.
'If bAnyColor is set to True, the lColorNumber variable will be ignored regardless of what it is set to
Dim rng As Range
Dim lCount As Long
Dim lCellColor As Long
For Each rng In rngCells
lCellColor = rng.Interior.Color
If bAnyColor Then 'count cells that are highlighted with any color
If rng.Interior.Pattern = xlNone Then
lCount = lCount + 1
End If
Else 'if a specific color number should be used
If lCellColor = lColorNumber Then
lCount = lCount + 1
End If
End If
Next
CountHighlightedCells = lCount
End Function
SECOND FUNCTION:
Function GetColorNumber(rng As Range) As Long
'returns the color number for the specified range. Use it to get the correct color number to use
'with CountHighlightedCells function
GetColorIndex = rng.Interior.Color
End Function
Originally Posted by
a4u2fear
anyone still coding? looks like both of your sheets do not have any VBA in them.