excel macro to loop through rows, while looping format rows if meet criteria?

Question

I'm having difficulty writing a macro to execute how I would like. I wish to search through all rows of a spreadsheet, if a particular column of the row contains matching text, then format the entire row as I would like (text color, borders, bold, etc..)

My current code only applies the formatting to the column with the matching text. See below:

Sub practice()

'loop for formatting subtotaled rows

Cells.Select

For Each mycell In Selection
If mycell.Value Like "*CO*" Then
Range(Selection, Selection.End(xlToRight)).Select
myrange.Font.Bold = True
myrange.Interior.ColorIndex = 35
End If
Next

End Sub

Answer 1

try this instead of the range(selection......



With mycell

.Font.Bold = True

.EntireRow.Interior.ColorIndex = 25

End With

Answer 2

This should work assuming Excel does not have a built in feature to do it. In an unused column put sequential numbers to be used to return the sequence at the end. In another column put 1 and then 2. Propagate it down so you have 1,2,1,2,1,2...... Now sort the sheet on the 1 and 2 column and every other row will be grouped together. Do the formatting. Resort using the sequential number column. Remove the two columns. ✩

Answer 3

adapt this



Selection.Rows(i).EntireRow.Delete



http://msdn.microsoft.com/fr-fr/library/microsoft.office.interop.excel.range.entirerow(VS.80).aspx