Recording macro gives Run-time error '9' Subscript out of range when it is runned (bug in recording macro)

I suggest that you have identified a bug but if you want to use xlAutomatic then use .Color in lieu of ColorIndex like the following.

    Sheets("Sheet1").Copy Before:=Sheets(1)
    ActiveSheet.Name = "Copy of Sheet1"
    With ActiveSheet.Tab
        .Color = xlAutomatic    'Use in lieu of next line
        '.ColorIndex = xlAutomatic  'Does not work
        .TintAndShade = 0
    End With

In the example on the MSDN website xlColorIndexNone is used. VBA does not record it correctly, when it uses xlAutomatic

Running the macro with xlAutomatic gives an error. When one changes xlAutomatic into xlColorIndexNone it runs fine. It is clearly a bug in VBA recording.

Sub Macro1()
    Sheets("Sheet1").Select
    With ActiveWorkbook.Sheets("Sheet1").Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End Sub

Hi,

right click on sheet tab

you can't have the xlAutomatic property

(but the 'no color' option  - xlNone / xlColorIndexNone)

so,

for what reason this is a bug ?

Hi,

right click on sheet tab

you can't have the xlAutomatic property

(but the 'no color' option  - xlNone / xlColorIndexNone)

so,

for what reason this is a bug ?

Try recording the macro as per the OP's original post and you will see that it records it as .ColorIndex = xlAutomatic as per the example below.

If it cannot record the code correctly so that the recorded code can be run without error then it is obviously a bug in macro recording.

I replied previously that xlAutomatic can be used with Color instead of ColorIndex and the code will run but having to edit the code because it did not record correctly is still a bug in the recording process. 

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet1").Select
    With ActiveWorkbook.Sheets("Sheet1").Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End Sub

Okay OssieMac, thank you.

OssieMac,

Thanks for confirming the bug.