Yes, you are right. You will have to evaluate each condition successively against the value of the cell, although $cell->{Value} will normally be enough. There will be complications if you are using dates and I dread to think what might happen if you are using array formulae. One bit of good news, however, is that Excel evaluates conditions serially, so that if the first matches, the others are ignored.

Regards,

John Davies

Massive update!

Depending on how your spreadsheets are created, there is a trick using names that allows you to continue to use conditional formulae but enables you to evaluate the conditions very easily. It's got other massive advantages, too. Here is some code to demonstrate it:

use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add();
for (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

$wb->Names->Add({Name=>"Fmt", RefersToR1C1=>"=RC>0"});         #Commen
+t 1
my $A1 = $wb->Sheets(1)->Cells(1, 1);
my $A2 = $wb->Sheets(1)->Cells(2, 1);
$A1->FormatConditions->Add({Type=>2, Formula1=>"=Fmt"});    #Comment 2
$A1->FormatConditions(1)->Interior->{Color} = 255;
$A1->Copy($A2);
PrintStatus();
$A1->{Value} = 1;
PrintStatus();
$A2->{Value} = 1;
PrintStatus();
$A1->{Value} = -1;
PrintStatus();

sub PrintStatus {
    my $seln = $xl->Selection;
    $A1->Select;                                            #Comment 3
    print $xl->Evaluate("Fmt") . " ";
    $A2->Select;
    print $xl->Evaluate("Fmt") . "\n";
    $seln->Select;
}
[download]

There is a common misconception in dealing with Excel. People refer to "named ranges". This was correct in 123, but Excel uses named formulae. Any time you create a name, the "RefersTo" property will start with an equals sign. They can also be as complex as any other formula.

Comment 1

This is where the name is created. A named formula called "Fmt" is created. It isn't necessary to use R1C1 notation, but it's clearer from this than from A1 notation that we are using an offset of no rows and no columns from some reference point (more on this later). The formula asks if the target cell is greater than zero, so it will return a boolean to Excel or VBA and 1 or 0 to Perl.

Comment 2

There are two main types of condition in a conditional format, namely "Cell value" and "Formula". If "Cell value" gets used, you have to write a parser. If "Formula" is used, you don't if you can replicate the formula. The formula we are entering, "=Fmt", could hardly be simpler (therefore, hardly easier to replicate). When we enter it as the condition for cell A1, that cell is used as the reference point for the offset, so it becomes the target cell itself. It is here that the difference between A1 and R1C1 format is useful. A user entering a name would not be likely to distinguish between a reference to A1 and one to $A$1. However, the difference is huge. The latter, which is Excel's default, will always point to the same cell. This is not what we want. When we copy, two lines later, we want the reference point and the target cell to change, too. Provided you are aware of this point, it doesn't matter which you use.

Comment 3

In Excel’s Select and Activate considered harmful, I rail against the use of Select. Your problem has led me to a very rare situation where it is necessary. To evaluate "Fmt", we have to have a reference point to get to the target cell. That is easy in the conditional format, as Excel takes the cell to be formatted as the reference point. But VBA and Perl need a different approach, which unfortunately is to use Select.

Other advantages

You don't repeat yourself as much. If you want to change the condition for formatting cells, you have to change it once only, in the named formula. You don't have to remember which cells have conditional formats and copy to them (I always get this wrong; don't you?). If you want to add the condition to subsequent cells that already have different formulae meaning that you can't copy, you have far less typing and far less risk of error.