excel conditional formatting

ArtYaffe has asked for the wisdom of the Perl Monks concerning the following question:

The following snippet of code isn't doing what I hoped it would do. After it runs the subject spreadsheet cell does not have a conditional format. What am I doing wrong? $WS is an Excel worksheet. $i is a row number.

use Win32::OLE::Const 'Microsoft Excel';
[...]
$WS->Cells($i,11)->FormatConditions ->Delete;
$WS->Cells($i,11)->FormatConditions->Add(xlExpression,,
   "=AND(J$i>0,K$i=0)");
$WS->Cells($i,11)->FormatConditions(1)->Interior->{ColorIndex}=
   xlYellow;
[...]
[download]

I have also tried, for the second line,

$WS->Cells($i,11)->FormatConditions->
   Add({Type} => xlExpression,{Formula1} => "=AND(J$i>0,K$i=0)");
[download]

and also

$WS->Cells($i,11)->FormatConditions->Add(xlExpression,
   undef,"=AND(J$i>0,K$i=0)");
[download]

A Macro to do what I want produces code that looks very much like what I've coded. Help!

Comment on excel conditional formatting Select or Download Code

Replies are listed 'Best First'.
Re: excel conditional formatting by bart (Canon) on Sep 10, 2009 at 15:46 UTC
This probably wil not be doing what you want: `$WS->Cells($i,11)->FormatConditions->Add(xlExpression,, "=AND(J$i>0,K$i=0)");` [download] I'm talking about the 2 commas in a row. Perl ignores extra commas. Try `$WS->Cells($i,11)->FormatConditions->Add(xlExpression, undef, "=AND(J$i>0,K$i=0)");` [download] (untested)	[reply] [d/l] [select]
Re^2: excel conditional formatting by ArtYaffe (Novice) on Sep 10, 2009 at 16:04 UTC
Thanks, Bart. That helped.I now get a conditional format, but the "=AND(J$i>0,K$i=0)" has been corrupted. With $i =11, I get "=AND(T21>0,U21=0)".	[reply]
Re^3: excel conditional formatting by ArtYaffe (Novice) on Sep 10, 2009 at 17:35 UTC
Thanks, Bart, for your help. 'undef' in the second position is necessary. However, this method does something "relative", so I had to add dollar signs to the formula. Also, although I have 'use Win32::OLE::Const "Microsoft Excel";' in the code, xlYellow didn't work; I substituted '6'. `$WS->Cells($i,11)->FormatConditions->Add (xlExpression,undef,"=AND(\$J\$$i>0,\$K\$$i=0)"); $WS->Cells($i,11)->FormatConditions(1)->Interior-> {ColorIndex} = 6; #xlYellow` [download] Problem solved. Thanks for everyone who chimed in.	[reply] [d/l]
Re: excel conditional formatting by o_chfa (Acolyte) on Sep 06, 2009 at 17:08 UTC
Have you something from `http://search.cpan.org/search?m=all&q=excel&s=11` installed? I know that it works (at least a couple of years ago ;-) )	[reply] [d/l]
Re^2: excel conditional formatting by ArtYaffe (Novice) on Sep 09, 2009 at 02:45 UTC
Are you referring to Spreadsheet::WriteExcel? It doesn't seem to do much for conditional formats. The only examples provided are for "cell less than" and "cell greater than". I need to do "Formula is". I have a 700 line script that works and does almost everything I need for it to do, but I haven't been able to automate the conditional formatting setups.	[reply]