Hi,

I'm trying to fill row 1 with colour but the no. of columns is dynamic. As such, I'm trying to write a code to detect the no. of columns and apply the colour. The program keeps giving me errors at the line in blue but I'm not sure what is wrong. Anyone can give advice?

Dim Range2 As String

Workbooks("QTP.xls").Activate
Worksheets("Trade_Participants").Select
Worksheets("Trade_Participants").Range("A1").Selec t
ActiveCell.CurrentRegion.Select
Range2 = Selection.Columns.Count
Worksheets("Trade_Participants").Cells(2, Range2).Select
Worksheets("Trade_Participants").Range(Selection, Selection.End(xlLeft)).Select
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With

Thanks

I think I understand your requirements, try this instead...

Dim myCols As Integer
Dim myRows As Integer

Worksheets(1).Select

myCols = Range("IV2").End(xlToLeft).Column
myRows = Range("A65000").End(xlUp).Row

Range(Cells(2, 1), Cells(2, myCols)).Select
Do Until ActiveCell.Row > myRows

    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
    ActiveCell.Offset(1, 0).Select
    myCols = Range("IV" & ActiveCell.Row).End(xlToLeft).Column
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, myCols - 1)).Select

Loop

cheers

Matt

the Cells object traditionally should be placed within a range object and you should have two dimensions in your range. In my example above ...
Range(Cells(2, 1), Cells(2, myCols)).Select
I have the starting cell Cell "A2" represented by the Cells(2,1) and the second cell reference Cells(2,mycols) is the dynamic reference according to the last column found on the row.

Is this helpful?

Matt

Hi, I tried to use the code you suggested but I'm still encountering "Run-time error '1004': Application-defined or object-defined error". What else could be wrong?

by the looks of it you are executing a module from outside the active workbook object, if this is the case replace the following line in my code...

Worksheets(1).Select

with these lines from your code...

Workbooks("QTP.xls").Activate
Worksheets("Trade_Participants").Select
Range("A1").select

cheers

Matt

Hi, tried as suggested but still the same error message.

Can you go into the code and press F8 to step through the code one line at a time. This will tell us which line is failing.

It all works fine on my PC setup.

Matt