Searching for color in cells
Posted by Jen on January 16, 2002 1:42 PM
I've got a 6000 row spreadsheet, in which a couple of rows are colored using the everyday Fill Color button.
Is there any way to run a search to find the rows (or cells?) that are colored?
Posted by Larry Kramer on January 16, 2002 3:00 PM
You can write a macro that tests each cell's .interior.colorindex property, e.g.:
Dim r as range
for each r in ActiveSheet.cells
if r.interior.colorindex=[color code] then
do stuff
end if
next r
I'm sure there's a list of colorindexes available, but I just cheat by selecting a cell manually and asking for activecell.interior.colorindex in the immediate pane. The colorindex an uncolored cell is -4142.)
I'm not sure that little loop works, but I am sure that if you can reference a cell as a range, that cell's interior.colorindex property is what you want.
Posted by Joe Was on January 16, 2002 3:10 PM
Copy this to a module or sheet code page. Assign a hot-key Tools-Macro-Macros-Options then add letter key.
To use the code select a starting cell (in the selected column of your cell should be a colored cell somewhere?
Run hot-key and the code will find the next cell that is colored and will select it.
To re-run the code and find the next cell with color. Select a cell below the cell just found. Run the hot-key again and the code will find the next colored cell down. Repeat until all your colored cell have been found. JSW
Sub myColor()
'By Joe Was
Dim myRowNum As Long
myRowNum = ActiveSheet.UsedRange.Rows.Count
Selection.Select
Do Until Selection.Row = myRowNum + 1
If Selection.Interior.ColorIndex <> xlNone Then
GoTo mySelect
Else
Selection.Offset(1, 0).Select
End If
Loop
End
mySelect:
End Sub
Hope this helps. JSW
Posted by Jennifer on January 16, 2002 3:59 PM
Cool, that did it - thanks! I've REALLY got to learn this stuff.
One question. What variable can I change to select the entire row instead of just a cell?
Posted by Joe Was on January 17, 2002 8:53 AM
Sub myColor()
'By Joe Was
Dim myRowNum As Long
myRowNum = ActiveSheet.UsedRange.Rows.Count
Selection.Select
Do Until Selection.Row = myRowNum + 1
If Selection.Interior.ColorIndex <> xlNone Then
Selection.EntireRow.Select
GoTo mySelect
Else
Selection.Offset(1, 0).Select
End If
Loop
End
mySelect:
End Sub Cool, that did it - thanks! I've REALLY got to learn this stuff. One question. What variable can I change to select the entire row instead of just a cell? : Copy this to a module or sheet code page. Assign a hot-key Tools-Macro-Macros-Options then add letter key. : To use the code select a starting cell (in the selected column of your cell should be a colored cell somewhere? : Run hot-key and the code will find the next cell that is colored and will select it. : To re-run the code and find the next cell with color. Select a cell below the cell just found. Run the hot-key again and the code will find the next colored cell down. Repeat until all your colored cell have been found. JSW : Sub myColor()
