CODEWITHSUNDEEP
excelpowershell
neeraja sreelaxmi
neeraja sreelaxmi

Reputation: 57

Powershell : Highlight excel cell Interior using condition

I want to highlight an excel cell interior using powershell , am able to change color of font , but not entire cell color

Excel Sheet

So this is how my excel sheet is , it checks whether the cell has "Matching" and changes color according to that

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Open("C:\Users\test4.xls")
$sheet = $workbook.ActiveSheet

$xlCellTypeLastCell = 11

$used = $sheet.usedRange 
$lastCell = $used.SpecialCells($xlCellTypeLastCell) 
$row = $lastCell.row # goes to the last used row in the worksheet
$column = $lastCell.Column; 
$i = 0 
for ($J = 2; $J -le $column; $j++) {
     $sheet.Cells.Item($i,$j).Interior.ColorIndex = 48
     $sheet.Cells.Item($i,$j).Font.Bold=$True
}
for ($i = 2; $i -le $row; $i++) {
    for($j = 5 ; $j -le $column ; $j++){
        if (($sheet.cells.Item($i,$j).Value()) -like "*Matching") {
            $sheet.Cells.Item($i,$j).Font.ColorIndex = 10
            $sheet.Cells.Item($i,$j).Font.Bold = $true
        }
        if(($sheet.cells.Item($i,$j).Value()) -like "*Not Matching"){
            $sheet.Cells.Item($i,$j).Font.ColorIndex = 3
            $sheet.Cells.Item($i,$j).Font.Bold = $true
        }
    }
}

$workbook.SaveAs("C:\Users\output.xls")
$workbook.Close()

this is code , Only problem is when i give

$sheet.Cells.Item($i,$j).Interior.ColorIndex = 48

It's giving me an exception

Exception from HRESULT: 0x800A03EC

I want my table to look like this , Output Excel Template

Upvotes: 1

Views: 3423

Answers (2)

postanote
postanote

Reputation: 16106

Extending from my comments.

PowerShell Excel and Conditional Formatting

# Quick Find the Top 10 Months
$xl = Open-ExcelPackage -Path $xlfile
Add-ConditionalFormatting -Worksheet $xl.StoresTop10Sales -Address $xl.StoresTop10Sales.dimension.address -RuleType Top -ForegroundColor white -BackgroundColor green -ConditionValue 10
Close-ExcelPackage $xl -Show

# Databars
Add-ConditionalFormatting -Worksheet $xl.StoresSalesDataBar -Address $xl.StoresSalesDataBar.dimension.address -DataBarColor Red

# Color Scales
Add-ConditionalFormatting -Worksheet $xl.StoresSalesTwoColorScale -Address $xl.StoresSalesDataBar.dimension.address -RuleType TwoColorScale

Use this module: ImportExcel 5.4.4

AddConditionalFormatting.ps1

Upvotes: 1

Dominique
Dominique

Reputation: 17555

So if I understand correctly, you are saying "I want to do with Powershell what Excel can do automatically using condional formatting", do you mean by that:

  • You are not aware of conditional formatting, or:
  • Conditional formatting means modifying something in your Excel file, which you can't do, hence the Powershell. Like this your question becomes "How to configure conditional formatting via Powershell?".

Which one is it?

Upvotes: 0

Related Questions