> On Apr 1, 3:48 pm, dlisterjr <dliste...@discussions.microsoft.com>
> wrote:
> > The problem is that I need to search each row for data and then format that
> > row base on if the data exist. The macro really didn't help with that.

> > "Oisin (x0n) Grehan [MVP]" wrote:

> > > On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com>
> > > wrote:
> > > > Is there a way to search a spreadsheet and then change the font color of the
> > > > row if the cell contains a string? IE if a cell in the row contains the word
> > > > "closed" (it won't be the only word in the cell), I want the whole row font
> > > > color red.

> > > Hi,

> > > Yes, this should be possible in PowerShell - I don't have the facility
> > > to test it here, but when usually when I need to do something like
> > > this, I record a macro in Excel of the actions I want to automate,
> > > then I view the VBA code in the editor IDE and translate/adapt it to
> > > powershell. You'll need an entry point into the Excel object model,
> > > which starts with:

> > > $excel = new-object -com excel.application

> > > ..and should end with:

> > > $excel.Quit()

> > > If you're still stuck on this later, drop a note back here and I'll
> > > help you finish it off.

> > > - Oisin- Hide quoted text -

> > - Show quoted text -

> I'm not sure I understand what you mean when you say "the macro didn't
> really help with that?" Did you record a macro of the actions you need
> to do in order to learn which parts of the object model you need to
> use?

> First, open a sample spreadsheet in Excel, then:

> 1) Start recording a Macro

> 2) CTRL+F search for your value

> 3) Stop recording the Macro

> 4) Examine the VBA code that was generated for searching the Excel
> sheet for a value by opening the VB editor in the macros menu and
> examining the Macro that you just recorded.

> 5) Adapt for PowerShell.

> Now, repeat the same actions for selecting a row in your spreadsheet
> and changing the background colour. If you need to know how to load a
> spreadsheet into Excel, you can record a macro for that too and
> examine the VBA code for hints.

> So now, you know which properties and methods to call on the Excel
> object model in order to a) find a value, and b) change the background
> colour of a row. It remains to use the $excel object above to perform
> the actions you need.

> Additionally, PowerShell lets you examine methods on any object by
> using the following syntax:

> ps> $excel = new-object -com excel.application
> ps> $excel | get-member

>    TypeName: Microsoft.Office.Interop.Excel.ApplicationClass

> Name                 MemberType Definition
> ----                 ---------- ----------
> ActivateMicrosoftApp Method     System.Void
> ActivateMicrosoftApp(XlMSApplication Index)
> AddChartAutoFormat   Method     System.Void AddChartAutoFormat(Object
> Chart, String Name, Object Description)
> AddCustomList        Method     System.Void AddCustomList(Object
> ListArray, Object ByRow)

> Nobody said it would be easy! :-)  If you're still completely confused
> by this, let me know.

> - Oisin