Categories

Set Background colour of Excel cell

I needed to set the background colour of a cell in an Excel spreadsheet recently.  The way to do it is to set the ColorIndex property of the Interior properties of the cell as shown in line 14.  The ColorIndex can be set to a number between 1 and 56.  Use –4142 if you don’t want a coloured background.

Using a ColorIndex is OK but what colours are related to the index values.  I found a VBScript amongst the Office information on the Microsoft site and modified it as shown below.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
$xl = New-Object -ComObject "Excel.Application"
$wkbk = $xl.WorkBooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)
$xl.Visible = $true

for($i=1; $i -le 56; $i++) {
    switch ($i) {
        {$_ -le 14}                {$row = $i;    $col = 1}
        {$_ -ge 15 -and $_ -le 28} {$row = $i-14; $col = 3}
        {$_ -ge 29 -and $_ -le 42} {$row = $i-28; $col = 5}
        {$_ -ge 43 -and $_ -le 56} {$row = $i-42; $col = 7}                   
    }
    $sheet.Cells.Item($row, $col).FormulaLocal = $i
    $sheet.Cells.Item($row, $col+1).Interior.ColorIndex = $i

}

$wkbk.SaveAs("c:\test\XLColours.xlsx")
$wkbk.Close()
$xl.Quit()

 

I am using Excel 2010 so WorkBooks.Add() will work OK.  If you are using an earlier version of Excel and are not using US English then you need to use the alternative method of adding workbooks as I discussed in earlier posts.

Create an object for Excel and then add a workbook and use the first worksheet. Make it visible so we can see what happens.

Use a for loop to work through the values 1 to 56.  I want the information arranging in 4 columns so use a switch statement to determine the row and column to use.

The ColorIndex value is displayed and the next cell on the row is coloured using that index.

We can then save and close the workbook.

Leave a Reply