Hi Dave,
The code below is as it appears below nothing has been changed, th
formulae are in B5 and B6 respectively B5 returns the count by colou
for column A just fine but the CntByColor returns a value of 0 but o
step thru does not use the CntbyColor function it uses the first
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 Functio
--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...nfo&userid=670
View this thread:
http://www.excelforum.com/showthread.php?threadid=27633