Question: How to match a keyword (stored in a variable) in a specific column and color a range?

[evild3ad]

I would like to match the keyword "Baseballs" (stored in a variable) in column A and color columns A-C of the row...but I don't get it working. Here are my attempts:

$Data = ConvertFrom-Csv @'
Item,Quantity,Price,Total Cost
Footballs,9,21.95,197.55
Cones,36,7.99,287.64
Shin Guards,14,10.95,153.3
Turf Shoes,22,79.95,1758.9
Baseballs,68,7.99,543.32
Baseball Gloves,31,65.00,2015.00
Baseball Bats,38,159.00,6042.00
'@

# Attempt 01 - Matches the keyword in column A but doesn't color the range A-C

$Keyword = "Baseballs"

$Data | Export-Excel -Path "$ENV:USERPROFILE\Desktop\Data1.xlsx" -FreezeTopRow -BoldTopRow -AutoSize -AutoFilter -WorkSheetname "Attempt1" -CellStyleSB {
param($WorkSheet)
# BackgroundColor and FontColor for specific cells of TopRow
$BackgroundColor = [System.Drawing.Color]::FromArgb(255,220,0)
Set-Format -Address $WorkSheet.Cells["A1:D1"] -BackgroundColor $BackgroundColor -FontColor Black
# HorizontalAlignment "Center" of columns B-D
$WorkSheet.Cells["B:D"].Style.HorizontalAlignment="Center"
Add-ConditionalFormatting -Address $WorkSheet.Cells["A:C"] -WorkSheet $WorkSheet -RuleType ContainsText -ConditionValue "$Keyword" -BackgroundColor Red
}

# Attempt 2 - Matches the keyword in column A and colors the range A-C, but it doesn't support the keyword as variable

$Data | Export-Excel -Path "$ENV:USERPROFILE\Desktop\Data2.xlsx" -FreezeTopRow -BoldTopRow -AutoSize -AutoFilter -WorkSheetname "Attempt2" -CellStyleSB {
param($WorkSheet)
# BackgroundColor and FontColor for specific cells of TopRow
$BackgroundColor = [System.Drawing.Color]::FromArgb(255,220,0)
Set-Format -Address $WorkSheet.Cells["A1:D1"] -BackgroundColor $BackgroundColor -FontColor Black
# HorizontalAlignment "Center" of columns B-D
$WorkSheet.Cells["B:D"].Style.HorizontalAlignment="Center"
Add-ConditionalFormatting -Address $WorkSheet.Cells["A:C"] -WorkSheet $WorkSheet -RuleType 'Expression' 'NOT(ISERROR(FIND("Baseballs",$A1)))' -BackgroundColor Red
}

[dfinke]

Single quotes with a variable in it do not get interpolated.

'$keyword' - no "$keyword" - yes

I used -f to do replacement using the single quotes.

$Data = ConvertFrom-Csv @'
Item,Quantity,Price,Total Cost
Footballs,9,21.95,197.55
Cones,36,7.99,287.64
Shin Guards,14,10.95,153.3
Turf Shoes,22,79.95,1758.9
Baseballs,68,7.99,543.32
Baseball Gloves,31,65.00,2015.00
Baseball Bats,38,159.00,6042.00
'@

$xlfile = "$PSScriptRoot\Data.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$Keyword = "Baseballs"
# $Keyword = "Cones"

$Data | Export-Excel -Path $xlfile -FreezeTopRow -BoldTopRow -AutoSize -AutoFilter -WorkSheetname "Attempt2" -Show -CellStyleSB {
    param($WorkSheet)

    # BackgroundColor and FontColor for specific cells of TopRow
    $BackgroundColor = [System.Drawing.Color]::FromArgb(255, 220, 0)
    Set-Format -Address $WorkSheet.Cells["A1:D1"] -BackgroundColor $BackgroundColor -FontColor Black

    # HorizontalAlignment "Center" of columns B-D
    $WorkSheet.Cells["B:D"].Style.HorizontalAlignment = "Center"

    $cv = 'NOT(ISERROR(FIND("{0}",$A1)))' -f $Keyword

    Add-ConditionalFormatting -Address $WorkSheet.Cells["A:C"] -WorkSheet $WorkSheet -RuleType 'Expression' -ConditionValue $cv -BackgroundColor Red
}

[evild3ad]

Awesome! This will help me a lot!