Repeat macro down a column till there's no input data?

[qprfan99]

The title probably isn't very well worded

I have a worksheet with a macro which formats a cell with the RGB values from the previous 3 cells.

So A1 will have the R value (0 - 255), B1 will have the G value (also 0 - 255) & C1 will have the B value (0 - 255)
I have a macro in D1 which will look up the 3 cells to the left &, if they contain valid numbers, will format that cell with the correct RGB colour.

This just works for 1 cell at a time.
I'd like it to repeat automatically until there is no more valid data in columns A, B or C.

Perhaps I should mention that the workshhet I am working on has about 135,000 rows so I need the macro to run 135,000 times (oe else I'm going to have to press CTRL Z 135,000 times which doesn't bear thinking about)

This is the macro

Sub Color()
Dim R As Integer, G As Integer, B As Integer
R = ActiveCell.Offset(0, -3).Value
G = ActiveCell.Offset(0, -2).Value
B = ActiveCell.Offset(0, -1).Value
ActiveCell.Interior.Color = RGB(R, G, B)
End Sub

I probably don't have to tell you that I'm very much a beginner at this.

Kind regards
Larry

 

[SteveO59L]

so are all the cells A1:D135000 populated?

 

[Dryver14]

this should work

Sub Color()
Dim R As Integer, G As Integer, B As Integer
Dim i As Long
Dim LastRow As Long


LastRow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To LastRow


    Cells(i, 4).Select


    R = ActiveCell.Offset(0, -3).Value
    G = ActiveCell.Offset(0, -2).Value
    B = ActiveCell.Offset(0, -1).Value
    ActiveCell.Interior.Color = RGB(R, G, B)
    
Next i
End Sub

 

[Dryver14]

by the way Steve059L's question is valid as if no number is returned at all to RGB then you get black

 

[qprfan99]

A1:C135000 are. Column D is blank waiting to be formatted.

 

[Dryver14]

that should happen

 

[qprfan99]

It got to row 65,372 then locked up.

On debugging it showed this error message

Run-time error '1004':
Too many different cell-formats.

I tried to check this on the worksheet but just got an Excel dialog box saying "Too many different cell formats"

Larry

 

[Dryver14]

In a standard excel sheet 2007 onwards you may have 64000 cell formats to get more you need to something with he registry which is beyond my skill set.

 

[qprfan99]

Ok thanks for your help.

I'll see if I can copy the rest into another workbook/sheet & do the other 70,000

Thx again
Larry

 

[Dryver14]

your welcome