Wednesday, July 29, 2009

How to show color indices in Excel with VBScript

Ok, someone asked me how to find out the color indices in Excel via Script. So here it goes:

(as always, formatting and word wrap might not work.....And you need Excel installed on the machine where the code executes of course)

-Script Begins-

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TrueSet
objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 14
objExcel.Cells(i, 1).Value = i
objExcel.Cells(i, 2).Interior.ColorIndex = i
Next

For i = 15 to 28
objExcel.Cells(i - 14, 3).Value = i
objExcel.Cells(i - 14, 4).Interior.ColorIndex = i
Next

For i = 29 to 42
objExcel.Cells(i - 28, 5).Value = i
objExcel.Cells(i - 28, 6).Interior.ColorIndex = i
Next

For i = 43 to 56
objExcel.Cells(i - 42, 7).Value = i
objExcel.Cells(i - 42, 8).Interior.ColorIndex = i
Next

-Script Ends-

No comments:

Post a Comment