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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
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,022
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,022
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. :(
 

Forum statistics

Threads
1,172,007
Messages
5,878,704
Members
433,364
Latest member
Jim M2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top