Share This Post

Creating a Microsoft Excel Spreadsheet With Excel Color Values VB Script

In response to questions I have received about Sending Vbs Results to Microsoft Excel files here is a brief script that will create a numeric list of the colors available for use with the Excel objects Interior and Font Color Indexes.

In my previous post examples I have lines that read as follows that sets the color values for the Interior (Background) and Font Colors:

After running the Vbs script below a Microsoft Excel workbook will open with the colors available for use with the Excel Object ColorIndex(s) and their associated numeric values.

To modify my original posts change the numeric values 19 or 11 with the colors numeric values of your choice.

For example if you want the font to appear as yellow (6) and the cells background to appear as a light gray (15) change the script lines above as follows:

objExcel.Selection.Interior.ColorIndex = 15
objExcel.Selection.Font.ColorIndex = 6

Vbs Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

For a = 1 to 56

objExcel.Cells(a, 1).Value = a

objExcel.Cells(a, 1).Interior.ColorIndex = a

Next

Share This Post

Don Hite began writing as a columnist for Rod Trent on SWYNK.Com (Now Enterpriseitplanet.Com) in the winter of 1999. In January of 2002 he co-founded the Kansas City Regional Systems Management Users Group (KCRSMUG) along with Janis Keim. Don is the eldest of four children and was born in the Army hospital at Wurzburg Germany in November of 1957. After living in Bad Kissingen Germany for the first few years of his life the Hite family moved back to the United States after his father retired from the army when Don was still in kaki army diapers. Educated by trade as a commercial master electrician Don made the career change from terminating Copper Conductor cable to terminating Ethernet cable in the early 1990’s. Don is married and lives on a farm in rural Kingsville Missouri with his wife Virginia (Ginny) and has a Son Lee, Stepdaughter Lisa and an 11 year old grandson named Blake.

Leave a Reply