Adding Color to Excel the Powershell Way


Want to add a little color to your spreadsheets?
This will show you how and create a sample spreadsheet with the 56 colors available to you.


###### Start Posh Script ########

$xl = new-object -comobject excel.application 
$xl.Visible = $true 
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add() 
$ws = $wb.Worksheets.Item(1) 
$row = 1 
$i = 1
For($i = 1; $i -lt 56; $i++){ 
 $ws.Cells.Item($row, 1) = "'$'ws.Cells.Item($row, 2).Font.ColorIndex = " + $row #<-- row 10 
 $ws.Cells.Item($row, 2).Font.ColorIndex = $row 
 $ws.Cells.Item($row, 2) = "test " + $row 
 $row++ 
}
[void]$ws.cells.entireColumn.Autofit()

############# End POSH Script ###############

Try taking the ‘ out from around the $ in row 10 and see what you get..
Be sure you do if you want to just cut and paste the cell.

Advertisements