Automatically Format Range of Cells

[Guest]

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

 

[Leith Ross]

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

 

[Guest]

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

 

[Steve Yandl]

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

 

[Gord Dibben]

Nigel

It will work.

Select B1 through P100

Format>CF>Formula is: =$B1="category"

Pick a color from Format>Patterns and OK your way out.

Every B 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

 

[Guest]

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.

 

[Guest]

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]

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