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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.