One way:

Dim Red_Range As Range
Dim Non_Red_Range As Range
Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.Font.ColorIndex = 3 Then
If Red_Range Is Nothing Then
Set Red_Range = rCell
Else
Set Red_Range = Union(Red_Range, rCell)
End If
Else
If Non_Red_Range Is Nothing Then
Set Non_Red_Range = rCell
Else
Set Non_Red_Range = Union(Non_Red_Range, rCell)
End If
End If
Next rCell
If Not Red_Range Is Nothing Then _
MsgBox "red range: " & Red_Range.Address(0, 0)
If Not Non_Red_Range Is Nothing Then _
MsgBox "non-red range: " & Non_Red_Range.Address(0, 0)



In article ,
"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.