formatting macro help

[bcelmer]

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

 

[Bernie Deitrick]

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

 

[bcelmer]

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

Works perfect. Thanks!