Hi all,

I have a problem looking up some data from a large table.

I have a list of dates ranging throughout the year, and have used the countif function to find the dates between certain periods, here I was using the month of the year. The problem is that some of these dates are in the font colour of red, and therefore need to be looked up and referenced differently.

I have;

21/04/06
27/04/06
27/04/06
17/05/06
24/05/06
29/06/06
28/07/06
23/08/06
31/08/06
28/09/06
tbc

I have been trying to use the references for VBA colours in text from Chips site;

Getting The Range Of Cells With A Specific Color

The following function will return a Range object consisting of those cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). This function uses the AddRange function to combine two ranges into a single range, without the possible problems of the Application.Union method. See AddRange, below, for more details about this function.

Function RangeOfColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Range
'
' This function returns a Range 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
If (Rng.Font.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
Else
If (Rng.Interior.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
End If
Next Rng

End Function

--and the add range VBA below;

Function AddRange(ByVal Range1 As Range, _
ByVal Range2 As Range) As Range
Dim Rng As Range

If Range1 Is Nothing Then
If Range2 Is Nothing Then
Set AddRange = Nothing
Else
Set AddRange = Range2
End If
Else
If Range2 Is Nothing Then
Set AddRange = Range1
Else
Set AddRange = Range1
For Each Rng In Range2
If Application.Intersect(Rng, Range1) Is Nothing Then
Set AddRange = Application.Union(AddRange, Rng)
End If
Next Rng
End If
End If

End Function

But I can't seem to get it to work properly with the referencing. this being in one of the cells =rangeofcolor(B11:B21,3,TRUE)

Becasue I am guessing that I first need to get the reference of the dates in red, and then use my countif formulae to find out how many of the dates fall within my specific criteria. hence my countif formulae of =COUNTIF(rangeofcolor(B11:B21,3,TRUE),">="&C3)

all I get is the #VALUE! Error.

Please please help, becasue I am really stuck with this one.

Cheers,

Dan