Automatically Format Range of Cells
 

In Column B I have several instances of the word Category, this instance
could be there 2 or 20 times depending on how much data is retrieved, what I
need to do is to have some code that searches column B and whenever it finds
the word category make the column range B to P bold and have a background
color of blue (for example) for that row, then the code would move on and
find the next instance and do the same thing

any suggestion would be appreciated

Thanks

Nigel

Automatically Format Range of Cells
 

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

Automatically Format Range of Cells
 

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

Automatically Format Range of Cells
 

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

Automatically Format Range of Cells
 

Nigel

It will work.

Select B1 through P100

FormatCFFormula is: =$B1="category"

Pick a color from FormatPatterns and OK your way out.

Every B:P row with the word "category" in B will be colored.

Note the $ sign in $B1. This anchors the lookup to Column B but allows the
row to increment.


Gord Dibben Excel MVP


On Sat, 3 Dec 2005 13:56:02 -0800, "Nigel" wrote:


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

Automatically Format Range of Cells
 

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

Automatically Format Range of Cells
 

With a bit of tweaking it worked perfectly, it was my stuff that needed
tweaking not yours
Final bit of code looked like this

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


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

Automatically Format Range of Cells
 

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