Submitted By: Kent Finkle

Changes the background color of a spreadsheet row if a date appears in a specified cell in that row.

PowerShell
Edit|Remove
$comments = @'
Script name: Change-ColorByDate.ps1
Created on: Sunday, June 10, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Change the Background Color of Spreadsheet Rows
That Have a Date in a Specified Cell?
'@
#-----------------------------------------------------
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.Open("C:\Scripts\Test1.xls")
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$i = 1
Do {
    $strValue = $objWorksheet.Cells.Item($i, 1).Value()
    $a = $strValue
    $b = $a -is [datetime]
    If ($b) {
        $objWorksheet.Cells.Item($i, 1).EntireRow.Interior.ColorIndex = 44
    }
    $i++
}
While ($objWorksheet.Cells.Item($i,1).Value() -ne $null)
$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)