CODEWITHSUNDEEP
excelpowershell
Marios Anagnostopoulos
Marios Anagnostopoulos

Reputation: 37

Color each excel row every time i find a search result

open the template.xls

search "ok" string

print adresslocal for each result found

color ColorIndex = 3 each row that "ok" appears

save excel sheet ,close

My problem is that i cant change the color for the result lines found though i print out the correct search results on Write-Host!!

This the code am working.

$file = 'C:\Users\bigadmin\Desktop\Projects\AutoUpdate\Template.xls' 

$xl = new-object -c excel.application

$wb1 = $xl.workbooks.open($file, $null, $true) 

$ws1 = $wb1.WorkSheets.item(1) 

$ws1.activate()

$Range = $WS1.Range("A100:D110")

$Target = $Range.Find("OK")

$First = $Target

Do
{

    Write-Host $Target.AddressLocal()

   $Target.EntireRow.Interior.ColorIndex = 3

    $Target = $Range.FindNext($Target)
}

While ($Target -ne $NULL -and $Target.AddressLocal() -ne 
$First.AddressLocal())


$wb1.Save() 

$wb1.close($true) 

$xl.quit()

spps -n excel

Upvotes: 0

Views: 125

Answers (1)

f6a4
f6a4

Reputation: 1782

Try this:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$file = 'C:\Users\bigadmin\Desktop\Projects\AutoUpdate\Template.xls' 

$searchFor            = 'Ok'
$colorIndex           = 3
$excel                = New-Object -ComObject Excel.Application
$excel.Visible        = $true
$excel.ScreenUpdating = $true

$workbook  = $excel.Workbooks.Open( $file ,$null, $false )

$ws1       = $workbook.WorkSheets.item(1) 

[void]$ws1.Activate()

$searchRange  = $ws1.Range("A100:D110")

$searchResult = $searchRange.Find( $searchFor, [System.Type]::Missing, [System.Type]::Missing, [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns )

while( $searchResult -and $ws1.Cells( $searchResult.Row, $searchResult.Column ).EntireRow.Interior.ColorIndex -ne $colorIndex  ) {

    $ws1.Cells( $searchResult.Row, $searchResult.Column ).EntireRow.Interior.ColorIndex = $colorIndex
    $searchResult = $searchRange.FindNext( $searchResult )
}

[void]$workbook.Save()
[void]$workbook.Close()
[void]$excel.Quit()

Upvotes: 1

Related Questions