Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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) "David" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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 -- Gary''s Student - gsnu200769 "David" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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! -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "David" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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. "Chip Pearson" wrote: 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) "David" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200769 "David" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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" wrote: 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! -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "David" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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 -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "David" wrote in message ... 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" wrote: 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! -- Saruman -------------------------------------------------------------------------- - All Outgoing Mail Scanned By Symantec Antivirus 10 -------------------------------------------------------------------------- - "David" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
Thanks,
Will try. Appreciate your help "Saruman" wrote: 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 -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "David" wrote in message ... 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" wrote: 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! -- Saruman -------------------------------------------------------------------------- - All Outgoing Mail Scanned By Symantec Antivirus 10 -------------------------------------------------------------------------- - "David" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting cells of a specific color only.
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 On Thu, 14 Feb 2008 00:01:06 -0000, "Saruman" wrote: 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! -- Saruman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional to change cell color by Selecting Cells | Excel Discussion (Misc queries) | |||
how to count cells with specific format (background color)? | Excel Discussion (Misc queries) | |||
Sum formula of cells with specific color value | Excel Worksheet Functions | |||
Selecting cells that contain text in a specific format | Excel Discussion (Misc queries) | |||
how to color code a row of cells based on a specific cell value | New Users to Excel |