You could loop through the cells:

Option Explicit
Sub testme()

Dim RngRed As Range
Dim Rng As Range
Dim myCell As Range

Set Rng = Selection 'activesheet.usedrange
For Each myCell In Rng.Cells
If myCell.Font.Color = vbRed Then
If RngRed Is Nothing Then
Set RngRed = myCell
Else
Set RngRed = Union(myCell, RngRed)
End If
End If
Next myCell

If RngRed Is Nothing Then
MsgBox "no red"
Else
RngRed.Select '????
End If

End Sub

SuperJas wrote:

Hi,

My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method:

* Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red.

i.e.
----------------------------------------------
Dim Red_Cells as Range

Set Red_Cells = {need help with code}
----------------------------------------------

* After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this?

Thanks very much for your help in advance! =)

SuperJas.

--

Dave Peterson