C
CheekyFlash
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
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