PDA

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



colorado_adv
07-08-2008, 11:28 AM
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

mae0429
07-08-2008, 11:34 AM
.row property?

Norie
07-08-2008, 11:40 AM
Try this.

With myCell.EntireRow
.Font.Bold = True
.Interior.ColorIndex = 35
End With

But I have to ask why you can't use conditional formatting for this.

mdmackillop
07-08-2008, 11:40 AM
Hi Colorado,
Welcome to VBAX
When you post code, use the VBA button to format it as shown.


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

colorado_adv
07-08-2008, 12:53 PM
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
07-08-2008, 01:04 PM
Norie,

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

mdmackillop
07-08-2008, 03:16 PM
As example
=FIND("CO",$E1,1)>0

colorado_adv
07-09-2008, 01:46 PM
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

with Range(MyCell, MyCell.End(xlToRight))

in place of

With MyCell.EntireRow

Any suggestions?

mdmackillop
07-09-2008, 01:50 PM
With Range(MyCell, Cells(MyCell.Row, Columns.Count).End(xlToLeft))

guatelize
08-22-2008, 01:22 AM
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
08-22-2008, 04:41 AM
Yes it is. Have a look at the attached workbook to give you an idea about how Shades has provided a solution.

guatelize
08-22-2008, 05:37 AM
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