Solved: Excel macro to loop through rows, while looping format rows if meet criteria

[colorado_adv]

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:

[VBA] 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[/VBA]

[mae0429]

.row property?

[Norie]

Try this.
[vba]
With myCell.EntireRow
.Font.Bold = True
.Interior.ColorIndex = 35
End With
[/vba]
But I have to ask why you can't use conditional formatting for this.

[mdmackillop]

Hi Colorado,
Welcome to VBAX
When you post code, use the VBA button to format it as shown.

[vba]
Sub practice()
'loop for formatting subtotaled rows
Dim MyCell As Range

For Each MyCell In ActiveSheet.UsedRange
If MyCell.Value Like "*CO*" Then
With MyCell.EntireRow
.Font.Bold = True
.Interior.ColorIndex = 35
End With
End If
Next
End Sub
[/vba]

[colorado_adv]

thank you all for your help.

I see that keeping my range to the used range cuts down on the macro run time severely as opposed to using the "cells.select".

[colorado_adv]

Norie,

How do I use conditional formatting to apply to a row based on criteria set for a single cell in that row?

[mdmackillop]

As example
=FIND("CO",$E1,1)>0

[colorado_adv]

Thanks for the additional help mdmackillop.

I tried making a slight adjustment to your code from yesterday so that the formatting would NOT apply to the entire row, but only to the used cells in that row. however, my formatting does not apply to the last 2 columns of data. I'm not sure why it is stopping short.

I'm using

[VBA]with Range(MyCell, MyCell.End(xlToRight))[/VBA]

in place of

[VBA]With MyCell.EntireRow[/VBA]

Any suggestions?

[mdmackillop]

[VBA]

With Range(MyCell, Cells(MyCell.Row, Columns.Count).End(xlToLeft))

[/VBA]

[guatelize]

Hello
Is it possible to have 4 or more conditions in th macro :

First : If MyCell.Value Like "*CO*" Then
Second : If MyCell.Value Like "*CD*" Then
Third : If MyCell.Value Like "*CE*" Then
Fourth : If MyCell.Value Like "*CF*" Then
Fifth : If MyCell.Value Like "*CG*" Then

Also with border lines and Bold ?

Thanks

Olivier

[Aussiebear]

Yes it is. Have a look at the attached workbook to give you an idea about how Shades has provided a solution.

[guatelize]

Thanks very much, but I'm trying to avoid or minimize calcultion with defined ranges in my sheets(= too large), it takes to much time to calculate the result.

I attached a file to demonstrate my problem with a pivot table.
I need 4 or more conditions to fill the subtotals and grand total with colors using conditional formatting with a formula in my pivot table. Every time the pivot table changes, the colors are following the movement. But I can not go further than 3 conditions.

Regards

Oliver