MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting colored cells


Posted by Artem on March 23, 2001 2:17 PM

Does anyone know a macro which whould select all cells with red text in a worksheet (or all blue text cells, or cells with certain other font/cell properties?) THANKS!


Posted by Ian on March 23, 2001 3:10 PM

Check out this posting

Posted by Celia on March 23, 2001 10:20 PM

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


Posted by Artem on March 24, 2001 12:48 PM

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!

Posted by Celia on March 24, 2001 5:43 PM


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

Posted by Celia on March 24, 2001 5:58 PM

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


Posted by Celia on March 24, 2001 6:04 PM

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

Posted by Artem on March 24, 2001 8:55 PM

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!!!