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

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

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!