Selecting colored cells

Check out this posting

12316.html

Artem

If you need further help, please advise
which of the following you want to be included in the selection? :-
1.All cells with red font cell format including those with no values, or
2.Cells with red font cell format but only those with a value.
and/or
3.Red fonts arising from number formatting (e.g. negative numbers in red) - if yes, what is the criteria for the red format?
and/or
4.Red fonts as a result of conditional formatting - if yes, what is the criteria?

Celia

hi Celia,

i want to select all cells on a worksheet with red font format but only those with a value (not conditional formats or negatives). i would be very grateful if you tell me a macro for that. thanks!

Artem

Sub Select_Red_Fonts()
Dim SearchRange As Range, cell As Range, redFonts As Range, x%
Set SearchRange = Cells.SpecialCells(xlCellTypeConstants)
For Each cell In SearchRange
If cell.Font.ColorIndex = 3 Then
If x = 1 Then
Set redFonts = Union(redFonts, cell)
Else
Set redFonts = cell
x = 1
End If
End If
Next cell
redFonts.Select
End Sub

Celia

Correction

Sub Select_Red_Fonts()

Correction :-
The above macro will not select cells with formulas. Try this instead :-

Sub Select_Red_Fonts()
Dim SearchRange As Range, cell As Range, redFonts As Range, x%
Set SearchRange = Union(Cells.SpecialCells(xlCellTypeConstants), _
Cells.SpecialCells(xlCellTypeFormulas, 23))
For Each cell In SearchRange
If cell.Font.ColorIndex = 3 Then
If x = 1 Then
Set redFonts = Union(redFonts, cell)
Else
Set redFonts = cell
x = 1
End If
End If
Next cell
redFonts.Select
End Sub

Celia

Revised Correction

Sorry, should be :-

Sub Select_Red_Fonts()
Dim SearchRange As Range, cell As Range, redFonts As Range, x%
Set SearchRange = Union(Cells.SpecialCells(xlCellTypeConstants), _
Cells.SpecialCells(xlCellTypeFormulas, 23))
For Each cell In SearchRange
If cell.Font.ColorIndex = 3 And cell.Value <> "" Then
If x = 1 Then
Set redFonts = Union(redFonts, cell)
Else
Set redFonts = cell
x = 1
End If
End If
Next cell
redFonts.Select
End Sub

Re: Revised Correction

Thanks Celia!

the revised macro does not work (run-time error '1004' No cells were found), but the very first one works perfectly!!!