Steve,

How do I incorporate this code, written below, into my spreadsheet? I have
never actually input code such as that into a spreadsheet before. Do you
just type it into the cell, or is there a different procedure for this?
Thanks.

"Steve Yandl" wrote:

Nigel,

See if something like this subroutine will do what you want.

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Set rngB = Sheets(1).UsedRange.Columns("B:B")
For R = 1 To rngB.Rows.Count
If Sheets(1).Cells(R, 2).Value = "Category" Then
Set rngTemp = Sheets(1).Cells(R, 2).Range("A1:O1")
rngTemp.Font.Bold = True
With rngTemp.Interior
..ColorIndex = 41
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With
End If
Next R
End Sub


Steve
"Nigel" wrote in message
...

Thanks, but this does not work for what I want, the word category could be
in any row (although it will only be in Column B), plus I want the row
where
the word category to get the new formating, so the first time I run it the
word could be in rows 2,17 and 23. The next time I run it the word could
be
in 4,6,9,14,23 etc.

I need to do a find of the word category and then format columns B to P
for
that row


"Leith Ross" wrote:


Hello Nigel,

You can make those changes automatically by using Conditional
Formatting. Select the Range of cells you want and go to Format on the
Menu and select *Conditional Formatting...* You can implement upto 3
conditional formats.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=490440

Just hold down the Alt key and press F8. Type the word "Decorate" for a
subroutine name and the click the 'Create' button. You can paste the
routine below over the sub and End sub lines that Excel will create for you
or simply type the guts of the routine between the two lines (don't
duplicate the sub and end sub lines is all).

You may want to take a look at the comments Gord posted regarding
Conditional Formatting. The only advantage to the subroutine I show over CF
is that it will determine the range without you having to guess the max
number of rows you might have. On the other hand, I didn't write anything
to clear formatting in the case where you run the routine a second time with
different values in column B (that could be done however).

To run the subroutine, just do Alt plus F8, pick the sub you want to run and
click the "run" button. Many people prefer to attach the subroutine to a
new tool button or use some event to trigger the action but I find it pretty
simple to do Alt + F8 most of the time if I'm the primary user.

Steve


"Gee-off" wrote in message
...
Steve,

How do I incorporate this code, written below, into my spreadsheet? I
have
never actually input code such as that into a spreadsheet before. Do you
just type it into the cell, or is there a different procedure for this?
Thanks.

"Steve Yandl" wrote:

Nigel,

See if something like this subroutine will do what you want.

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Set rngB = Sheets(1).UsedRange.Columns("B:B")
For R = 1 To rngB.Rows.Count
If Sheets(1).Cells(R, 2).Value = "Category" Then
Set rngTemp = Sheets(1).Cells(R, 2).Range("A1:O1")
rngTemp.Font.Bold = True
With rngTemp.Interior
..ColorIndex = 41
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With
End If
Next R
End Sub


Steve
"Nigel" wrote in message
...

Thanks, but this does not work for what I want, the word category could
be
in any row (although it will only be in Column B), plus I want the row
where
the word category to get the new formating, so the first time I run it
the
word could be in rows 2,17 and 23. The next time I run it the word
could
be
in 4,6,9,14,23 etc.

I need to do a find of the word category and then format columns B to P
for
that row


"Leith Ross" wrote:


Hello Nigel,

You can make those changes automatically by using Conditional
Formatting. Select the Range of cells you want and go to Format on the
Menu and select *Conditional Formatting...* You can implement upto 3
conditional formats.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=490440