Presumably there is some logic to the application of the colors. You would
make your life a lot simpler, and your worksheet a lot faster to calculate, if
you put that logic into a formula in another column that returns 1 if the cell
is to be colored, then did your counts on that helper column.

As Earl Kiosterud mentions on his web site, you should NOT try to store
information in formatting. Excel was designed to interpret and manipulate
information that's stored as numbers or text.


On Wed, 10 Nov 2004 23:46:20 -0600, Simon Lloyd
wrote:


Hi Dave,

The code below is as it appears below nothing has been changed, the
formulae are in B5 and B6 respectively B5 returns the count by colour
for column A just fine but the CntByColor returns a value of 0 but on
step thru does not use the CntbyColor function it uses the first 3
lines and then uses the Count By Color its wierd!

Any suggestions?.....the functions are caled upon by the Auto_open

Simon

Sub Auto_open()

Dim ccount As Integer
Dim cccount As Integer
Application.DisplayAlerts = False
Application.DisplayFormulaBar = False


Range("B5").Select
ActiveCell.FormulaR1C1 =
"=COUNTBYCOLOR(R[9]C[-1]:R[484]C[-1],38,FALSE)"
Range("B7").Select
Range("d14").Select
ccount = Range("b5")
Range("B6").Select
ActiveCell.FormulaR1C1 = "=CntByColor(R[8]C[2]:R[485]C[33],38,FALSE)"
'Range("B7").Select
'Range("d14").Select
Range("B6") = cccount

Worksheets("holidays").Visible = True
Worksheets("Holiday Count").Visible = True
Worksheets("Xtra's & Count").Visible = True
Sheets("holidays").Activate
MsgBox "There Are " & ccount & " Holiday Clashes" & Chr(13) & "
There Have Been " & cccount & " accomodations", vbOKOnly, "Clash Count"


End Sub



Function CountByColor(InRange As Range, WhatColorIndex As Integer,
Optional OfText As Boolean = False) As Long
Dim Rng As Range
Application.Volatile True

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

End Function


Function CntByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

Dim Rng1 As Range
Application.Volatile True
Rng1 = Range("D14:AI491")
For Each Rng1 In InRange.Cells
If Rng1.Value = "#N/A" Then
'do nothing
Else
If IsNumber = True Then
CntByColor = CntByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CntByColor = CntByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If

Next Rng1

End Function