How to cut a selection of rows and paste into another workbook & repeat

Question

I have a spreadsheet with 15000 rows in one column. I want to create a script to colour code each cell descending down the spreadsheet. What I need is:

• First 10 cells in RED
• Next 5 cells in BLUE
• Next 5 cells in GREEN
• Repeat this for all cells descending down from A1 to A1500...

Any quick pointers? They had an admin girl doing this manually for three days :/

-----EDIT

Thanks guys for the responses, extremely helpful!

But - It turns out I have misunderstood the full scale of what is. Quite complex now that I realise what is needed.

What we have is: four workbooks in one spreadsheet (tier 1a, tier 1b, tier 1c, sheet1)

The script needs to:

• Cut the first 10 cells in tier 1 and paste into sheet1 column A;
• Cut the first 5 cells in tier 2 and paste into sheet1 column A;

• Cut the first 5 cells in tier 3 and paste into sheet1 column A;

• Repeat in descending order for all cells in each workbook - so the end result will have 10-5-5 10-5-5 10-5-5 values etc. in sheet1 column A

Any help would be greatly appreciated :) otherwise manual it is.. please save my sanity

Answer 1

Here is a simple macro to do it

Sub colorRBGRow()

   For X = 0 To 14980 step 20
    Range("A" & X + 1 & ":A" & X + 10).Interior.ColorIndex = 3
    Range("A" & X + 11 & ":A" & X + 15).Interior.ColorIndex = 5
    Range("A" & X + 16 & ":A" & X + 20).Interior.ColorIndex = 4

    Next X
End Sub

Answer 2

You can use conditional formatting: eg for the first color (assuming your data begin on row 2) use the formula:

=AND(MOD(ROW()-1,20)>0,MOD(ROW()-1,20)<11) 

Next color:

=AND(MOD(ROW()-1,20)>10,MOD(ROW()-1,20)<16)

Last one is trickier:

=OR(MOD(ROW()-1,20)=0,AND(MOD(ROW()-1,20)>15,MOD(ROW()-1,20)<20))

Answer 3

Try using this formula and conditional formatting to colour the cells:

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)

Now I use R1C1 mode, rather than A1 mode, so just replace the RC with the cell you are formatting and then copy paste the formula into the other cells.

In any case this formula returns the number 1, 2, or 3 representing the colours RED, BLUE, and GREEN.

I then formatted every cell RED, and then it's just a case of applying two conditional formats for BLUE and GREEN:

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)=2

And:

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)=3

Here's my result:

Answer 4

You can actually auto-fill colors. You can do this with VBA or manually.

VBA:

Sub Macro1()
    Range("A1:A20").Select
    Selection.AutoFill Destination:=Range("A1:A15000"), Type:=xlFillDefault
End Sub

Do this on a blank sheet - you can copy-paste this color format onto any cells you want.

You can use an autofill type of xlFillFormats if you don't want to do any copy-pasting but it will override any other (non-color related) formats as well:

Sub Macro2()
    Range("A1:A10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A11:A15").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A16:A20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1:A20").Select
    Selection.AutoFill Destination:=Range("A1:A109"), Type:=xlFillFormats
End Sub