Counting Function Dilemma
 

Hi All,

I have some code in my Auto_open that references a function to count by
colour only if a date exists which works fine, my problem is i have
tried to replicate it but to count over a range only if the cell
contains text (or if easier could get it to look for a number entered
in the cell between 1 and 10) but if the cell contains #N/A to either
skip it or when counting delete it from the total.

Here's what i have so far, and the second half doesnt work!

simon



Sub Auto_open()

Dim ccount As Integer
Dim cccount As Variant
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
cccount = Range("B6")
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 Rng As Range
Dim cccount As Integer
Application.Volatile True
Range("D14:AI491") = Rng
For Each Rng In InRange.Cells
If OfText = True Then
If ActiveCell.Text = "#N/A" Then
Range("B6").Value = Range("B6").Value - 1
CntByColor = CntByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CntByColor = CntByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=276228