=COUNTBYCOLOR(B$4:B$111,CELLCOLORINDEX($A117,FALSE ),FALSE)
and the following are the functions I pasted into the VBA




Function FindShades(a As Range) As Integer
FindShades = 0
For Each c In a
If c.Interior.ColorIndex < xlColorIndexNone Then
FindShades = FindShades + 1
End If
Next c
End Function



Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function


Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function


"JLatham" wrote:

Can we see the formula?

Have you tried [F9] - not automatically refreshing, but might work and it
would be a one-key-click solution.

"Ted" wrote:

i have a formula that counts cells that have been highlighted.
But I have to redrag it to get it to refresh after I highlight cells...
can someone tell me how to get it to automatically refresh after I highlight
cells?

Please help...
Thanks