[Ted]

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

[JLatham]

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

[Ted]

=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

[JLatham]

Well, I don't know what we can do. My next suggestion was going to be to
(maybe) add
Application.Volatile
as part of the code, but it is already there.

What you can do that may be easier than doing the drag operation is to pick
an empty cell on the sheet and type a number, any number, into it. The
formula should update then. You can either leave the number in that 'force
update' cell, or delete it.

"Ted" wrote:

=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

[Ted]

Thanks,

I think your F-9 button thing works just fine.
I added it as a Macro to a button that says: REFRESH FORMULAS.

"JLatham" wrote:

Well, I don't know what we can do. My next suggestion was going to be to
(maybe) add
Application.Volatile
as part of the code, but it is already there.

What you can do that may be easier than doing the drag operation is to pick
an empty cell on the sheet and type a number, any number, into it. The
formula should update then. You can either leave the number in that 'force
update' cell, or delete it.

"Ted" wrote:

=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

[JLatham]

Great! I posted the 2nd post 'cause I thought maybe it didn't work, I hadn't
tested it at that time so I just went looking for another way without testing
[F9] myself.

"Ted" wrote:

Thanks,

I think your F-9 button thing works just fine.
I added it as a Macro to a button that says: REFRESH FORMULAS.

"JLatham" wrote:

Well, I don't know what we can do. My next suggestion was going to be to
(maybe) add
Application.Volatile
as part of the code, but it is already there.

What you can do that may be easier than doing the drag operation is to pick
an empty cell on the sheet and type a number, any number, into it. The
formula should update then. You can either leave the number in that 'force
update' cell, or delete it.

"Ted" wrote:

=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