Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search excel for cells with a color
I am trying to run a search for all cells in a spreadsheet that have a
specific background color. The good news is I can do it and view the list of all formatted cells. I would like to then copy the contents of these cells and place them on another sheet. However, I am having trouble copying the contents and moving them. Can anyone provide any suggestions? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
search excel for cells with a color
How about a macro?
Option Explicit Sub testme() Dim CurWks As Worksheet Dim RptWks As Worksheet Dim FoundCell As Range Dim destCell As Range Dim FirstAddress As String Application.FindFormat.Clear With Application.FindFormat.Interior .ColorIndex = 6 'whatever you want here! End With Set CurWks = Worksheets("sheet1") Set RptWks = Worksheets.Add Set destCell = RptWks.Range("a1") With CurWks.UsedRange Set FoundCell = .Cells.Find(What:="", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=True) If FoundCell Is Nothing Then 'do nothing MsgBox "not found!" Else FirstAddress = FoundCell.Address Do FoundCell.Copy _ Destination:=destCell destCell.Offset(0, 1).Value = FoundCell.Address(0, 0) Set destCell = destCell.Offset(1, 0) Set FoundCell = .Cells.Find(What:="", _ After:=FoundCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=True) Loop While Not FoundCell Is Nothing _ And FoundCell.Address < FirstAddress End If End With End Sub The .findnext() stuff seemed to forget about the searchformat parm. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm john mcmichael wrote: I am trying to run a search for all cells in a spreadsheet that have a specific background color. The good news is I can do it and view the list of all formatted cells. I would like to then copy the contents of these cells and place them on another sheet. However, I am having trouble copying the contents and moving them. Can anyone provide any suggestions? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color coding the excel sheets | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel Object copied to Word not printing in color | Excel Discussion (Misc queries) | |||
Changing color palette for Excel 2002 | Excel Discussion (Misc queries) |