Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting macro help
Hi,
I have a report that has a different number of columns each day. I have the macro highlighting rows, 2 highlighted, 2 blank. But I only want the highlighting to extend to x number of columns. One problem is each data set has blanks in it, so I can't use the EndxlToRight for every row. I need to replace the "Z" in the statement Range("A1:Z2") with the x variable. Can you please help? Dim x As Long Range("A4").Select Range(Selection, Selection.End(xlToRight)).Select x = Selection.Columns.Count Range("A1").Select Do ActiveCell.Offset(4, 0).Range("A1:Z2").Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Loop Until ActiveCell.Offset(4, 0) = "" Thanks! Bret |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting macro help
You could use
x =Cells(4,Columns.Count).End(xlToLeft).Column or, better, in case row 4 is missing something in the last column x = Range("A4").CurrentRegion.Columns.Count And use Do ActiveCell.Offset(4, 0).Resize(1,x).Select to incorporate x into your formatting routine. HTH, Bernie MS Excel MVP wrote in message ... Hi, I have a report that has a different number of columns each day. I have the macro highlighting rows, 2 highlighted, 2 blank. But I only want the highlighting to extend to x number of columns. One problem is each data set has blanks in it, so I can't use the EndxlToRight for every row. I need to replace the "Z" in the statement Range("A1:Z2") with the x variable. Can you please help? Dim x As Long Range("A4").Select Range(Selection, Selection.End(xlToRight)).Select x = Selection.Columns.Count Range("A1").Select Do ActiveCell.Offset(4, 0).Range("A1:Z2").Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Loop Until ActiveCell.Offset(4, 0) = "" Thanks! Bret |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting macro help
On Mar 10, 8:35*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: You could use x =Cells(4,Columns.Count).End(xlToLeft).Column or, better, in case row 4 is missing something in the last column x = Range("A4").CurrentRegion.Columns.Count And use *Do * *ActiveCell.Offset(4, 0).Resize(1,x).Select to incorporate x into your formatting routine. HTH, Bernie MS Excel MVP wrote in message ... Hi, I have a report that has a different number of columns each day. *I have the macro highlighting rows, 2 highlighted, 2 blank. *But I only want the highlighting to extend to x number of columns. *One problem is each data set has blanks in it, so I can't use the EndxlToRight for every row. *I need to replace the "Z" in the statement Range("A1:Z2") with the x variable. Can you please help? Dim x As Long * *Range("A4").Select * *Range(Selection, Selection.End(xlToRight)).Select * *x = Selection.Columns.Count * *Range("A1").Select * *Do * *ActiveCell.Offset(4, 0).Range("A1:Z2").Select * *With Selection.Interior * * * *.ColorIndex = 15 * * * *.Pattern = xlSolid * *End With * *Loop Until ActiveCell.Offset(4, 0) = "" Thanks! Bret Works perfect. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting using Macro | Excel Discussion (Misc queries) | |||
Formatting for a Macro | Excel Discussion (Misc queries) | |||
Formatting via a macro | Excel Discussion (Misc queries) | |||
Conditional Formatting in Macro | Excel Discussion (Misc queries) | |||
Date Formatting in a Macro | Excel Discussion (Misc queries) |