title: vbscript Excel Match Function
body: <p>I have a worksheet with some 100k rows by perhaps 2 dozen columns. Presently I am coloring a specific column, say "ABC", that when the value is > x set the interior.colorindex to y. At the moment I have to sort this column descending, then using a FOR EACH statement, cycle through each of the row cells until the value &lt; x, coloring the cell as each NEXT is reached.</p>
  
  <p>What I am trying to be is make this far more efficient by using the Excel MATCH function, find the last row number, then color the cells in one block rather than individual cells but cannot get my clumsy coding to work correctly. Everything I have read appears to indicate that the MATCH function is supported in vbscript, but I need some assistance from some kind soul figure this out. I have trimed my code down to the relevant section and would appreciate and assistance provided. Please forgive my ignorance, I am very new to this coding thing, and this is my first post requesting help.</p>
  
  <pre><code>Dim objXLApp, objXLWb, objXLWs, objWorksheet, WorksheetFunction
  Dim InFile, OutFile
  Dim ObjRange, ObjRange2, ObjRange3, rng, rng1, rng2, trng
  Dim iRows, iCols, iR, iC, lRow, fRow, col, rw, tRow
  Dim ColSearch, StartTime, EndTime, TotalTime
  Dim cTeal, cPurple, cCyan, cVal, opVal
  Dim Counttcolor, Countpcolor, Countccolor, clr
  Dim vMsg 
  
  ' input parameters
  InFile = Wscript.Arguments.Item(0)
  OutFile = Wscript.Arguments.Item(1) 'this output file CAN be the same as the input thereby overwriting if required.
  
  Set objXLApp = CreateObject("Excel.Application")
  
  'application function SWITCHES - set to TRUE to enable
  objXLApp.Visible          = True
  objXLApp.EnableEvents     = True
  objXLApp.DisplayAlerts    = True
  objXLApp.ScreenUpdating   = True
  objXLApp.DisplayStatusBar = False
  vMsg = 1 ' set to 1 to turn on timer prompts for each processing section
  
  Set objXLWb = objXLApp.Workbooks.Open(InFile)
  
  'Select the appropriate Sheet in the Workbook
  Set objXLWs = objXLWb.Sheets(1)
  
  objXLWb.Sheets(1).Activate
  objXLWs.DisplayPageBreaks = False
  
  'decleration must be AFTER opening the input file
  objXLApp.Calculation = xlCalculationManual 
  objXLApp.CalculateBeforeSave = True
  
  ' Set range and count Row &amp; Columns
  Set objRange = objXLWs.UsedRange
  iRows = objRange.Rows.Count
  iCols = objRange.Columns.Count
  'MsgBox iRows
  'MsgBox iCols
  
  StartTime = Timer()
  ColSearch = "ABC" 'COLUMN AS
  For iC = 1 To iCols
      If InStr(objRange.Item(1, iC).Value2,ColSearch) Then
          'sort the column descending to bring highest records to the top
          Set objRange = objXLWs.UsedRange
          Set objRange2 = objXLApp.Range(objRange.Item(2, iC).Address) 'ABC
          objRange.Sort objRange2, xlDescending, , , , , , xlYes
  
          cTeal = 15 'set the teal minimum value
  
          'set the range for the match function to search for the min cTeal value
          rng = objRange.Item(2, iC).Address &amp;":"&amp; objRange.Item(iRows, iC).Address 
          'search for the first row number containing the first value less than cTeal
          tRow = objXLApp.match(cTeal, rng, -1)
          MsgBox tRow 'this presently fails here with object required if commented fails at set trng with reference to tRow variable
  
          'set the range for coloring the entire block of cells
          Set trng = objRange.Item(2, iC).Address &amp;":"&amp; objRange.Item(tRow, iC).Address
          objXLApp.Range(trng).Interior.ColorIndex = 42 'Teal
      End If
  Next
  
  EndTime = Timer()
  If vMsg = 1 Then MsgBox "ABC: " &amp; FormatNumber(EndTime - StartTime, 2)
  </code></pre>