Submitted By: Kent Finkle

Assigns values to cells based on background color, and then sums these assigned values.

PowerShell
Edit|Remove
$comments = @'
Script name: Sum-ByColor.ps1
Created on: Tuesday, May 01, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Assign a Background Color to Cells in a
Spreadsheet and Then "Sum" Those Cells?
'@

# -----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------

$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True

$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$objWorksheet.Cells.Item(1, 1).Value() = "A"
$objWorksheet.Cells.Item(2, 1).Value() = "B"
$objWorksheet.Cells.Item(3, 1).Value() = "C"
$objWorksheet.Cells.Item(4, 1).Value() = "D"
$objWorksheet.Cells.Item(5, 1).Value() = "E"
$objWorksheet.Cells.Item(6, 1).Value() = "F"
$objWorksheet.Cells.Item(7, 1).Value() = "G"
$objWorksheet.Cells.Item(8, 1).Value() = "H"

$objWorksheet.Cells.Item(1, 1).Interior.ColorIndex = 7
$objWorksheet.Cells.Item(2, 1).Interior.ColorIndex = 8
$objWorksheet.Cells.Item(3, 1).Interior.ColorIndex = 9
$objWorksheet.Cells.Item(4, 1).Interior.ColorIndex = 10
$objWorksheet.Cells.Item(5, 1).Interior.ColorIndex = 7
$objWorksheet.Cells.Item(6, 1).Interior.ColorIndex = 7
$objWorksheet.Cells.Item(7, 1).Interior.ColorIndex = 8
$objWorksheet.Cells.Item(8, 1).Interior.ColorIndex = 10

$i = 1

Do {

    $intColor = $objExcel.Cells.Item($i, 1).Interior.ColorIndex

    switch ($intColor) {
        7 {$intSum += 5}
        8 {$intSum += 10}
        9 {$intSum += 15}
        10 {$intSum += 20}
    }

    $i++
}

While ($objWorksheet.Cells.Item($i,1).Value() -ne $null)

Write-Host($intSum)

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)