get range of cells based on colour
 

Hi - have been trying to use code from
http://www.cpearson.com/excel/colors.htm which enables me to return a
cell range based on background colour of cells. However, the code gives
me an "Error: Sub or function not defined" message. Can someone tell me
why (other functions for counting cells based on colour work just
fine)?

Code is:

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

Thanks in advance.
skellis

get range of cells based on colour
 

You will also need to copy the AddRange function found further down the
page.

Hope this helps
Rowan

skellis wrote:
Hi - have been trying to use code from
http://www.cpearson.com/excel/colors.htm which enables me to return a
cell range based on background colour of cells. However, the code gives
me an "Error: Sub or function not defined" message. Can someone tell me
why (other functions for counting cells based on colour work just
fine)?

Code is:

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

Thanks in advance.
skellis