Expand a range
 

Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells to the
right of it all the way over to Col. K (not the whole row). I have the first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column Offset but
that seems awfully clunky to me and I want to do this the right way. Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
..Font.Bold = True
..Font.ColorIndex = 3
End With
End If
Next cell
End Sub

Expand a range
 

Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick


"cottage6" wrote in message
...
Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells to
the
right of it all the way over to Col. K (not the whole row). I have the
first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column Offset
but
that seems awfully clunky to me and I want to do this the right way.
Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub

Expand a range
 

Maybe not the best way to do it but one that would work is to run a for next
statement that would go through and check column a and then change the color
for the rest of the row to red if column a starts with wire. See below.

dim x as integer
dim strIsItWire as string

For x = 2 to LastRow
strIsItWire = Range("A" & x).Value
If Left(strIsItWire, 4) = "WIRE" Then
Range("A" & x & ":K" & x).Select
With Selection
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next


cottage6 wrote:
Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells to the
right of it all the way over to Col. K (not the whole row). I have the first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column Offset but
that seems awfully clunky to me and I want to do this the right way. Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub

--
Message posted via http://www.officekb.com

Expand a range
 

Thanks to both of you for your solutions. Rick, always good to hear from you

"Rick Rothstein (MVP - VB)" wrote:

Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick


"cottage6" wrote in message
...
Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells to
the
right of it all the way over to Col. K (not the whole row). I have the
first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column Offset
but
that seems awfully clunky to me and I want to do this the right way.
Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub

Expand a range
 

Rick, always good to hear from you

The way you said that makes me think I have helped you before over in the
compiled VB newsgroups... is that the case (sorry if I don't remember you,
but the name "cottage6" doesn't ring a bell)?

Rick



Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick


"cottage6" wrote in message
...
Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells
to
the
right of it all the way over to Col. K (not the whole row). I have the
first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column
Offset
but
that seems awfully clunky to me and I want to do this the right way.
Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub

Expand a range
 

You help me all the time; on 3/13/08 with "Input Box Help", on 5/2/08 with
"Get date from prior sheet", and with an awesome user form that pops up a
list of files, just to name a few. I'm not sure what you mean by compiled VB
newsgroups, but thanks again for all your help.

"Rick Rothstein (MVP - VB)" wrote:

Rick, always good to hear from you

The way you said that makes me think I have helped you before over in the
compiled VB newsgroups... is that the case (sorry if I don't remember you,
but the name "cottage6" doesn't ring a bell)?

Rick



Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick


"cottage6" wrote in message
...
Hi everyone,
I need to find rows in Col. A with the word "Wire" as the 1st four
characters, and then bold and turn red that cell along with the cells
to
the
right of it all the way over to Col. K (not the whole row). I have the
first
part of the macro figured out, but am unsure how to expand the range to
include the extra columns to the right. I can do it using Column
Offset
but
that seems awfully clunky to me and I want to do this the right way.
Here's
what I have so far, and thanks for any help you can provide!

Sub BoldTheRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set CompareRange = Range("A1:A" & LastRow)
For Each cell In CompareRange
If Left(cell, 4) = "WIRE" Then
With cell
.Font.Bold = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub