Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.

[Guest]

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 Rang

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]

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

 

[Dick Kusleika]

SuperJas

Try this

Sub NonRedCells()

Dim NonRed As Range
Dim cell As Range

For Each cell In Sheet1.UsedRange.Cells
If cell.Font.Color <> vbRed Then
If NonRed Is Nothing Then
Set NonRed = cell
Else
Set NonRed = Union(NonRed, cell)
End If
End If
Next cell

MsgBox NonRed.Address

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

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.

'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?

 

[Tom Ogilvy]

This assumes the font color is not produced by conditional formatting.

Dim red_cells as Range
Dim non_red_cells as Range
Dim cell as Range
for each cell in Activesheet.UsedRange
if cell.Font.ColorIndex = 3 then
if redcells is nothing then
set red_cells = cell
else
set red_cells = Union(red_cells,cell)
end if
else
if nonredcells is nothing then
set non_red_cells = cell
else
set non_red_cells = Union(non_red_cells,cell)
end if
end if
Next

--
Regards,
Tom Ogilvy

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.

'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?

 

[JE McGimpsey]

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)

 

[Guest]

Thanks Dave! Thanks Dick! Thanks Tom! Thanks JE

All these work fantastic! =) Very happy now! =)