Hi I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

Thanks.


Sub formatcolumns()

    Columns("G:G").Insert Shift:=xlToRight
    Range("H1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("N1").FormulaR1C1 = "=(RC[-1])"
    Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"
    Columns("N:N").Interior.ColorIndex = 36
    Columns("R:R").Insert Shift:=xlToRight
    Range("R1").FormulaR1C1 = "=(RC[-1])"
    Range("R2:R").FormulaR1C1 = "=(RC[-4]/RC[-2])"
    Columns("R:R").Interior.ColorIndex = 36
    Columns("U:U").Insert Shift:=xlToRight
    Range("U1").FormulaR1C1 = "=(RC[-1])"
    Range("U2:U").FormulaR1C1 = "=(RC[-14]*RC[-2])"
    Columns("U:U").Interior.ColorIndex = 36
    Columns("AC:AC").Insert Shift:=xlToRight
    Range("AC1").FormulaR1C1 = "=(RC[-1])"
    Range("AC2:AC").FormulaR1C1 = "=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
    Columns("AC:AC").Interior.ColorIndex = 15
    Columns("AJ:AJ").Interior.ColorIndex = 35
    Columns("AN:AN").Insert Shift:=xlToRight
    Range("AN1").FormulaR1C1 = "=(RC[-1])"
    Range("AN2:AN").FormulaR1C1 = "=(RC[-4]-RC[-33])"
    Columns("AN:AN").Interior.ColorIndex = 35
    Columns("AR:AR").Insert Shift:=xlToRight
    Range("AR1").FormulaR1C1 = "=(RC[-1])"
    Range("AR2:AR").FormulaR1C1 = "=(RC[-4]/RC[-3])"
    Columns("AR:AR").Interior.ColorIndex = 35
    Columns("BB:BD").Select
    Selection.Cut
    Columns("L:L").Insert Shift:=xlToRight
    Columns("M:N").Select
    Selection.Cut
    Columns("R:R").Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("D:D").Insert Shift:=xlToRight
    Range("N2").Select
    Selection.AutoFilter
    ActiveWindow.FreezePanes = True
    Columns("AG:AG").Select
    Selection.Interior.ColorIndex = 34
    Columns("AH:AH").Select
    Selection.Interior.ColorIndex = 33
End Sub