Expand a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cottage6 - 31 Jul 2008 14:39 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 31 Jul 2008 15:06 GMT

Change your With statement from this...

With cell

to this....

With cell.Resize(1, 11)

Rick

> Hi everyone,
> I need to find rows in Col. A with the word "Wire" as the 1st four
[quoted text clipped - 21 lines]
> Next cell
> End Sub

Reply to this Message

cottage6 - 31 Jul 2008 15:42 GMT

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

> Change your With statement from this...
>
[quoted text clipped - 31 lines]
> > Next cell
> > End Sub

Reply to this Message

Rick Rothstein (MVP - VB) - 31 Jul 2008 16:01 GMT

> 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...
>>
[quoted text clipped - 33 lines]
>> > Next cell
>> > End Sub

Reply to this Message

cottage6 - 31 Jul 2008 16:37 GMT

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, always good to hear from you
>
[quoted text clipped - 41 lines]
> >> > Next cell
> >> > End Sub

Reply to this Message

dustinbrearton - 31 Jul 2008 15:16 GMT

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

>Hi everyone,
>I need to find rows in Col. A with the word "Wire" as the 1st four
[quoted text clipped - 17 lines]
>Next cell
>End Sub

Reply to this Message