Is there a smart way to select multiple rows based on #2 :)

M

Mike

Many thanks to Bob Kilmer for his great suggestion in the last question on a similar vein
Does anyone know how to manipulate this "replace" formula to work on cells with a set internal.colorindex value ?

Column("A:A").Selec
Selection.Replace What:="", Replacement:="", LookAt:=xlWhole,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True,
ReplaceFormat:=Fals

How do you weave in a "Interior.ColorIndex = 5" as the search criteria ?? Basically, on the same vein as before, if a cell in column "A" has a fill color of "5", for example, then set the value of the ID to "null" - later that whole row can be deleted using "Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp

If this is possible, again very curious as would be a neat method. Can't find any decent reference online for this
TI

Mike
 
B

Bob Phillips

Hi Mike,

The 'standard' way of working with Colorindex is to add a helper column and
derive the colorindex in there. And then do any work on that column. Prob
lem with search and replace of course is that you would want to search on
one column and replace on the other. It would probably be best with a Find
loop and manually replace when found.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Mike said:
Many thanks to Bob Kilmer for his great suggestion in the last question on a similar vein.
Does anyone know how to manipulate this "replace" formula to work on cells
Click to expand...
with a set internal.colorindex value ??
Column("A:A").Select
Selection.Replace What:="", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False

How do you weave in a "Interior.ColorIndex = 5" as the search criteria ??
Click to expand...
Basically, on the same vein as before, if a cell in column "A" has a fill
color of "5", for example, then set the value of the ID to "null" - later
that whole row can be deleted using
"Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp"
If this is possible, again very curious as would be a neat method. Can't
Click to expand...
find any decent reference online for this.
 
M

Mike

Thanks Bob

I'm using a loop now... so I guess I'll stick with that. the helper column sounds like a nice idea... again a nice workaround..
may sneak that in somewhere
Many thanks again

Mike
 
Top