Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range that expand | Excel Discussion (Misc queries) | |||
Expand range help | Excel Programming | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Expand Range | Excel Programming | |||
Find first cell in range and expand range -VBA | Excel Programming |