title: Speed up Excel PowerShell modification to file
body: <p>I have a process which will look for a value in a cell and compare it with an array, then it will replace that value with the corresponding value from the second array.</p>
  
  <p>This script is working, but I'm looking into how to tune it and make it fast.</p>
  
  <pre><code>$ColorCodes = "001", "002", "003"
  $FAColors = "Blue", "Red", "White"
  $XLSXDoc = "c:\Users\w0517\Desktop\ColorCodes.xlsx"
  $SheetName = "Sheet1"
  $Excel = New-Object -ComObject "Excel.Application"
  $Workbook = $Excel.workbooks.open($XLSXDoc)
  $Sheet = $Workbook.Worksheets.Item($SheetName)
  $WriteData = $Excel.WorkSheets.Item($SheetName)
  $RowCount = ($Sheet.usedRange.rows).count
  write-host The Excel Sheet Has $RowCount Rows
  For ($J=2; $J -le $RowCount; $J++)
      {
         $MainColor = $Sheet.Cells.Item("$J",2).Text
         $ArrayIndex = [array]::IndexOf($FAColors, $MainColor)
         $WriteData.Cells.Item("$J",2) = $ColorCodes[$ArrayIndex] 
  
      }
  $Excel.Visible = $true
  </code></pre>