Macro to color fill rows?

J

Jennifer

I know Excel doesn't read fill color but can you make a macro that finds all
cells with the word "total" in it and fill color that entire row?

Thanks in advance for any help!
Jennifer
 
G

Gary''s Student

Try:

Sub ordinate()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Value = "total" Then
r.EntireRow.Interior.ColorIndex = 6
End If
Next
End Sub

I picked 6 - yellow
 
J

Jennifer

Thanks for your help. However, it only works if "total" is the ONLY word in
the cell... but what if "total" if ONE of the words in the cell? Such as ABC
Total, CDE Total, etc.
Is that possible?

Thanks.
Jennifer
 
D

Dave Peterson

One way:

If r.Value = "total" Then
becomes
If lcase(r.Value) like "*total*" Then
 
D

David McRitchie

Hi Jennifer,
If you are open to additional solutions after having one that works,
I think Conditional Formatting would be easier to work with as you
would not have to invoke a macro and it is builtin.

In fact thanks to a posting by Jason Morin, it is already on my page.
http://www.mvps.org/dmcritchie/excel/condfmt.htm#find

Select cell A1 then use Ctrl+A to select all cells (press 2x if 2003)
That should have cell A1 as the active cell all all cells on page selected.
Format, Conditional Formatting
Formula is: =COUNTIF(1:1,"*total*")
Then use formatting controls there to format such tings as
font attributes such as color and boldface, interior color (pattern color)
 
P

Paddy

David McRitchie said:
If you are open to additional solutions after having one that works,
I think Conditional Formatting would be easier to work with as you
would not have to invoke a macro and it is builtin.

In fact thanks to a posting by Jason Morin, it is already on my page.
http://www.mvps.org/dmcritchie/excel/condfmt.htm#find

Select cell A1 then use Ctrl+A to select all cells (press 2x if 2003)
That should have cell A1 as the active cell all all cells on page
selected.
Format, Conditional Formatting
Formula is: =COUNTIF(1:1,"*total*")
Then use formatting controls there to format such tings as
font attributes such as color and boldface, interior color (pattern
color)
Click to expand...

Interesting, David.

However, I can't see how to distinguish between making a cell active and at
the same time selecting a number of cells to conditionally format.

Say I want to test the contents of ONE cell, and colour the ROW according to
the contents of THAT cell?

Example: IF contents of B14 equal zero then make cells A14:H14 font grey
ELSE make cells A14:H14 font blue.

Thanks

Paddy
 
D

David McRitchie

Hi Paddy,
I think the questions are answered on my page on Conditional Formatting
except for the part of based on the color of another cell, which cannot
be done in C.F. http://www.mvps.org/dmcritchie/excel/condfmt.htm

In Conditional Formatting, you must choose the color, from
the formatting button and menus within C.F. Read the red
highlighted text on the page. There are formulas that you do
not see that are applied to every cell that is selected when you
set up a Conditional Format, each of those formulas is based
on the active cell and is offset much in the same manner as
you use formulas generated through the fill handle or the
shortcut Ctrl+D. http://www.mvps.org/dmcritchie/excel/fillhand.htm

The formula supplied in my reply used 1:1 and refers to the
range column 1 of the current row (active cell on row 1). On
row 2 the formula would be treated as row 2 as it is offset
by one row from the entered formula. You can test by
entering the formula on a cell in Row 1 and using the fill handle
to copy down and looking at the formula on the other rows.
The C.F. formula would not actually change but that is how it is
effectively treated/used.
 
P

Paddy

David McRitchie said:
I think the questions are answered on my page on Conditional Formatting
except for the part of based on the color of another cell, which cannot
be done in C.F. http://www.mvps.org/dmcritchie/excel/condfmt.htm
Click to expand...

Thanks for your help, David. I worked out a method that turned out to be
fairly simple.

I used Conditional Format using two conditions:

Condition 1: Formula is: =$B14="" Format all cells in row font
grey.
Condition 2: Formula is: =NOT($B14="") Format all cells in row font blue.

Select that entire (used) row; use Format Painter to transfer to all other
used rows.

Thus if cell B?? is empty, the rest of that row is in grey font; if cell B??
has an entry, the rest of that row is in blue font.

Voila !

Thanks for your hints.

Paddy
 
Top