Set Cell BGColor from DTS Package?

[JeffK627]

I have a DTS package that creates an Excel workbook and writes data to it. That part works fine, but I am trying to set some formatting from a VBScript and it isn't working. Here is the relevant code snippet:

Dim s

    For Each s in objXLB.Worksheets

        With s.Range("A1:IV65536").Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .ColorIndex = xlAutomatic
            objLogFile.WriteLine("Font formatted.")
        End With

        With  s.Range("A1:IV1")
            .Font.FontStyle = "Bold"
            .Interior.ColorIndex = 15
            .Interior.Pattern = xlSolid
            objLogFile.WriteLine("Top row formatted.")
        End With

    Next

The font face, size, style (bold) and color are all fine, but it is not setting the background color - it apparently is ignoring the ".Interior.ColorIndex = 15" part. Can anyone help?

 

[JeffK627]

Bringing this back to page 1. Anyone?

 

[bandit_1981]

Check your worksheet protection, If you are using excel 2000 you will need to unprotest it then change it back to protected when done.

 

[JeffK627]

The sheets are not protected. Besides, the font formatting is working, it just won't set the background color.

 

[bandit_1981]

I just pasted your code in a blank excel 2000 workbook minus your file writing and everything worked just fine. So not real sure what your issue is but will see if i cant figure it out

 

[JeffK627]

It works fine in an Excel workbook, but I need to run it from a VBScript in a DTS package. I can't just call a macro because the DTS package creates the workbook on the fly.

Everything works except the background color.

 

[Greg Truby]

On the standard pallet, 15 should be "grey25". However, if you are calling this from outside Excel, I don't know if the standard pallet has definition. You might try using

.Interior.Color = RGB(192,192,192)

instead. Just a shot in the dark...

 

[JeffK627]

Greg, I just tried your suggestion and got an error message (thrown from the DTS package):

Unable to set the ColorIndex property of the Interior class

This implies that it recognizes Interior but won't set the ColorIndex for some reason...

 

[Greg Truby]

Did you notice that I was setting the .color property; not the .colorindex property?

 

[JeffK627]

Well, I noticed now...

Tried it the right way, no more error message, but it still didn't set the color.