PSExcel.HighlightLine.ps1

PSExcel.HighlightLine.ps1

<#
    This code illustrates highlighting a line based on a cell value
        Search-CellValue will help identify the rows we care about
        Format-Cell will format these rows
    
    Prerequisite: Download and load up PSExcel http://ramblingcookiemonster.github.io/PSExcel-Intro/
#>

# View the help on the primary commands we will be using
    Get-Help Search-CellValue -Full
    Get-Help Format-Cell -Full

#Set up the data
    $File = 'D:\VarEg.xlsx'
    $WorksheetName = 'WS1'
    $DemoData = 1..10 | Foreach-Object{

        $EID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$EID)
    
        New-Object -TypeName PSObject -Property @{
            Name = "jsmith$_"
            EmployeeID = $EID
            Date = $Date
            FlagThis = (Get-Random -InputObject $false, $True)
        } | Select Name, EmployeeID, Date, FlagThis
    }

    $DemoData | Export-XLSX -Path $File -WorksheetName $WorksheetName -force

# Find rows that have a false in them. Note that if you have false in another column, you'll have to filter out that column before expanding the row.
    $Rows = Search-CellValue -Path $File -WorkSheetName $WorksheetName -FilterScript {$_ -eq $false} | Select -ExpandProperty Row

#We have rows... Loop through them and set background to red
    $Excel = New-Excel -Path $File
    $WorkSheet = $Excel | Get-WorkSheet -Name $WorksheetName
    foreach($Row in $Rows)
    {
        Format-Cell -StartRow $Row -EndRow $Row -WorkSheet $WorkSheet -BackgroundColor Red
    }
    $Excel | Close-Excel -Save
    
#View the damage. Red is a jarring color!
    ii $File