Expand a range

[cottage6]

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

 

[Rick Rothstein \(MVP - VB\)]

Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick

 

[dustinbrearton via OfficeKB.com]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[cottage6]

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.