Range based on interior color index?

[garle]

Is there a way for me to define a range based on a particular interior
color index value? For example, can I "select" a group of cells on a
sheet that have a color index of 38?

Thanks in advance for any help!

 

[Norman Jones]

Hi Garle,

I think that you eould need to loop through the cells and bulid the required
range, e.g.:

'=============>>
Public Sub Tester3()
Dim Rng As Range
Dim Rng2 As Range
Dim rCell As Range

Set Rng = Range("A1:K100")

For Each rCell In Rng.Cells
If rCell.Interior.ColorIndex = 38 Then
If Rng2 Is Nothing Then
Set Rng2 = rCell
Else
Set Rng2 = Union(rCell, Rng2)
End If
End If
Next rCell

Rng2.Select

End Sub
'<<=============

 

[garle]

Thank you, Norman, works great!

I tried converting the sub to a function that returns the range of
cells with a given interior color index value as follows:

Function findColorInRange(searchrange As range, colorix As Integer)
Dim resultrange As range
Dim rCell As range

For Each rCell In searchrange.Cells
If rCell.Interior.ColorIndex = colorix Then
If resultrange Is Nothing Then
Set resultrange = rCell
Else
Set resultrange = Union(rCell, resultrange)
End If
End If
Next rCell

findColorInRange = resultrange

End Function


However, I get an error (run-time error 91) when I try to call with:

Sub testing()
Dim rng As range

rng = findColorInRange(range("database"), 38)

End Sub

Any ideas? I have the feeling I'm missing something basic, but I don't
know much about calling routines in macros.

 

[a.ohlund]

Hi garle, You have to state what you want to return from the function,
like:

Function findColorInRange(searchrange As range, colorix As Integer) As
Range

As you thought, it was just something basic! : )

//Anders
ExcelSpecialisten SWE

 

[garle]

Thank you, Anders.

I still got an error after this change (but it got thrown in the
function, not the sub). I fiddled around and eventually added 'set' to
the assignments in both the calling sub and the function and was able
to get it to work properly.

Can anyone explain why I need to use:

Set findColorInRange = resultrange // from function

and

Set rng = findColorInRange(range("database"), 38) // from calling sub

I guess I've been clear on when to use 'set" and when not to...