The OP probably doesn't need this anymore, but since I couldn't find anything on the topic myself, here's what I came up with. There are two major problems with the OP's code, plus a minor typo.

Problem 1: xlCellValue and xlExpression have to be barewords, not strings. For that to work, we need use Win32::OLE::Const 'Microsoft Excel';, which was probably set correctly anyway.

Problem 2: Multiple parameters for VBA (or OLE, I'm not certain which is correct to say here) functions can't just be listed, we have to use dictionaries with named parameters. The parameter names can be found in the VBA help that comes with the VB editor in MS Office, for example. The first, mandatory, parameter precedes that dictionary (or "can precede", I'm not certain here).

So, the working code should look something like the following:

use Win32::OLE::Const 'Microsoft Excel';
[...]
my $Range = $Book->Worksheets('Name')->Range("H1");
$Range->FormatConditions ->Delete;
$Range->FormatConditions->Add(xlTextString,
   { String => "finished",
     TextOperator => xlEndsWith });
$Range->FormatConditions(1)->Interior->{ColorIndex} = 3;
$Range->FormatConditions(1)->Font->{ColorIndex} = 2;
$Range->FormatConditions->Add(xlTextString,
   { String => "in progress",
     TextOperator => xlEndsWith });
$Range->FormatConditions(1)->Interior->{ColorIndex} = 3;
$Range->FormatConditions(1)->Font->{ColorIndex} = 5;

[download]

This is not the OP's formatting condition, since I didn't want to look up the parameter names needed for that. It should be fairly simple to adapt this to your needs.

In reply to Solution to Conditional Formatting in Excel by SilverSnitch
in thread FormatConditions Error by Topodo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.