Selecting cells of a specific color only.

[David]

Is there a way to find and select cells only of a certain color in order to
speed the formating of these cells. Any help would be appreciated.

Thanks David

 

[Chip Pearson]

David,

You can use the code below to do this. The function RangeOfColor returns a
range containing the cells of the OfRange parameter which have a ColorIndex
equal to the ColorIndex parameter. If the OfText parameter is omitted or
False, the background fill color is tested. If OfText is True, the font
color of each cell is tested. You can then call this function with code
similar to the SelectRangeOfColor procedure below.

Function RangeOfColor(OfRange As Range, _
ColorIndex As Long, Optional OfText As Boolean) As Range
Dim ResRange As Range
Dim R As Range

For Each R In OfRange.Cells
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
If ResRange Is Nothing Then
Set ResRange = R
Else
Set ResRange = Application.Union(ResRange, R)
End If
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
If ResRange Is Nothing Then
Set ResRange = R
Else
Set ResRange = Application.Union(ResRange, R)
End If
End If
End If
Next R
Set RangeOfColor = ResRange

End Function

Sub SelectRangeOfColor()

Dim RR As Range
Dim ColorIndex As Long
Dim OfText As Boolean

ColorIndex = 3 ' RED. See help on ColorIndex for other values
OfText = False ' FALSE -> test fill color, TRUE -> test font color

If TypeOf Selection Is Excel.Range Then
If Selection.Cells.Count = 1 Then
MsgBox "Select the range to test.", vbOKOnly
Else
Set RR = RangeOfColor(Selection, ColorIndex, OfText)
If Not RR Is Nothing Then
RR.Select
End If
End If
Else
MsgBox "Select a range of cells and try again", vbOKOnly
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

Here is an example for Yellow#6:

Sub FindYellow()
Set r = Nothing
For Each rr In ActiveSheet.UsedRange
If rr.Interior.ColorIndex = 6 Then
If r Is Nothing Then
Set r = rr
Else
Set r = Union(rr, r)
End If
End If
Next
If r Is Nothing Then
Else
r.Select
End If
End Sub

 

[Saruman]

In Excel 2003, you can use the find Tab of Find And Replace to locate
coloured cells.

Open a find and replace form

Leave the Find What: field blank

Click the options button

Click the dropdown arrow on the Format Button

Select the Choose Format From Cell option

Now click in a cell with the format that you want to amend

Click the Find All Button and cells with that formatting are shown in the
lower window.

Click each one to go to that cell and make format changes as required in
each cell.

Hope this helps!

 

[David]

Chip,

Appreciate your fast response however I was hoping there was a way to do
this procedure by using the existing Excel menus or if you explain to me how
to use this code. I am a novice when it comes to programing.

 

[David]

Appreciate your fast response however I was hoping there was a way to do
this procedure by using the existing Excel menus or if you explain to me how
to use this code. I am a novice when it comes to programing.

 

[David]

Using this process, will I be able to replace all selected cell formats on a
bulk basis.
Basicall I am trying to select all yellow highlighted cells(input fields)
and unprotect them. If there is an easier way, please let me know.

Thanks for your help.

 

[Saruman]

Yes

Slight change to procedure

Use the Replace Tab

Same other buttons, but in the Replace dropdown Format arrow, select a cell
with the colour format that you require.

Find All then Replace All.

Try on a copy of the original first until you perfect it.

May need to format a blank cell with a colour you want first so you can
select it during the replace operation of the formatting

 

[David]

Thanks,

Will try. Appreciate your help

Yes

Slight change to procedure

Use the Replace Tab

Same other buttons, but in the Replace dropdown Format arrow, select a cell
with the colour format that you require.

Find All then Replace All.

Try on a copy of the original first until you perfect it.

May need to format a blank cell with a colour you want first so you can
select it during the replace operation of the formatting

 

[Gord Dibben]

In addition to Saruman's suggestion.

After hitting the Find All button as instructed below, in the Found Dialog Box
you can hit SHIFT + End to select all found cells and apply formatting to all at
once if desired.


Gord Dibben MS Excel MVP