Set Cell BGColor from DTS Package?

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
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:

Code:
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?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bandit_1981

Board Regular
Joined
Aug 17, 2005
Messages
201
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

Active Member
Joined
Jun 22, 2005
Messages
313
The sheets are not protected. Besides, the font formatting is working, it just won't set the background color.
 

bandit_1981

Board Regular
Joined
Aug 17, 2005
Messages
201

ADVERTISEMENT

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

Active Member
Joined
Jun 22, 2005
Messages
313
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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
Code:
.Interior.Color = RGB(192,192,192)
instead. Just a shot in the dark...
 

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
Greg, I just tried your suggestion and got an error message (thrown from the DTS package):

Code:
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Did you notice that I was setting the .color property; not the .colorindex property?
 

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
Well, I noticed now... :oops:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,010
Members
412,304
Latest member
citrus
Top