#!perl
use strict;
use Win32::OLE::Const 'Microsoft Excel';
Win32::OLE->Option(Warn => 3);

my $ex = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
        
$ex->{Visible}=1;
my $wb = $ex->Workbooks->Open('c:\\temp\\ole.xlsx') ;
my $ws = $wb->sheets(1);

# select range
my $sel = $ws->Range("A1:A3");

# add conditional formatting
my $fc = $sel->{'FormatConditions'};
$fc->AddColorScale({ColorScaleType=>3});
$fc->{$fc->{'Count'}}->SetFirstPriority;

# set color scale criteria
my $csc = $fc->{1}->{'ColorScaleCriteria'};
$csc->{1}->{'Type'} = xlConditionValueLowestValue;
$csc->{1}->{'FormatColor'}->{'Color'}=8109667;
$csc->{1}->{'FormatColor'}->{'TintAndShade'}=0;

$csc->{2}->{'Type'} = xlConditionValuePercentile;
$csc->{2}->{'Value'} = 50;
$csc->{2}->{'FormatColor'}->{'Color'}=8711167;
$csc->{2}->{'FormatColor'}->{'TintAndShade'}=0;

$csc->{3}->{'Type'} = xlConditionValueHighestValue;
$csc->{3}->{'FormatColor'}->{'Color'}=7039480;
$csc->{3}->{'FormatColor'}->{'TintAndShade'}=0;
[download]

Thank you all and special thanks to Monk Poj. I printed the original VB and your Perl code and will study them side-by-side. I did review the Excel object model through Excel and on the MSDN site, but that was new to me also. So, having your Perl code will go a long, long way in helping understand how to translate between the two languages. I'm so very grateful to have the whole picture to get me on my journey.

Poj, I did have to make a couple of changes for the example you so wonderfully provided to work on my machine.

For the lines setting the Type, like this one: $csc->{1}->{'Type'} = xlConditionValueLowestValue; I needed to put quotes around the value like this: $csc->{1}->{'Type'} = 'xlConditionValueLowestValue';

When I used this line to start Excel: my $excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); I got this error:

Win32::OLE(0.1709) error 0x80020005: "Type mismatch"
    in PROPERTYPUT "Type" at OleExcelTipsTricks3.pl line 42.
[download]

However, no other code changes were needed if I launched Excel this way: my $excel = CreateObject OLE "Excel.Application";

No clue yet, why the difference in launching Excel mattered in my case, but just wanted to share that in case it matters for someone else.

You may find this helpful also:

Thank you CoVAX. These three related links were helpful, but not quite enough to get me over the hump. Grateful for them, though: http://docs.activestate.com/activeperl/5.6/faq/Windows/ActivePerl-Winfaq12.html#convert_vba http://www.corion.net/talks/vb2perl/vb2perl.en.html http://www.perlmonks.org/?node_id=153486

What do you have so far? It is easier and usually much more educational to guide your code toward your goal, as opposed to guessing what you want or what obstacles you have encountered.

#!c:\perl\bin
use strict;
use OLE;

$Win32::OLE::Warn = 3;    # Die on errors.

# Start an instance of the Excel application
my $excel = CreateObject OLE "Excel.Application";

# Make Excel visible or not
$excel->{visible}=1;    # 1=True, 0=False

# Create a new workbook (Excel file)
my $workbook = $excel->Workbooks->Add();

# Create and name a worksheet
my $worksheet = $workbook->Worksheets("Sheet1");
$worksheet->Activate();
$worksheet->{Name} = "Color Scale Example";

# Put some values in a range of cells in Column A
$worksheet->Range("a1")->{Value} = 87;
$worksheet->Range("a2")->{Value} = 74;
$worksheet->Range("a3")->{Value} = 16;
$worksheet->Range("a4")->{Value} = 24;
$worksheet->Range("a5")->{Value} = 48;

# Select the cells with the values to be color scaled
my $selection = $worksheet->Range("A1:A5");

# Add conditional formatting
my $formatConditions = $selection->{'FormatConditions'};
$formatConditions->AddColorScale({ColorScaleType=>3});
$formatConditions->{$formatConditions->{'Count'}}->SetFirstPriority;

# Set the color scale criteria which has three components
my $colorScaleCriteria = $formatConditions->{1}->{'ColorScaleCriteria'
+};
$colorScaleCriteria->{1}->{'Type'} = 'xlConditionValueLowestValue';
$colorScaleCriteria->{1}->{'FormatColor'}->{'Color'}=8109667;
$colorScaleCriteria->{1}->{'FormatColor'}->{'TintAndShade'}=0;

$colorScaleCriteria->{2}->{'Type'} = 'xlConditionValuePercentile';
$colorScaleCriteria->{2}->{'Value'}=50;
$colorScaleCriteria->{2}->{'FormatColor'}->{'Color'}=8711167;
$colorScaleCriteria->{2}->{'FormatColor'}->{'TintAndShade'}=0;

$colorScaleCriteria->{3}->{'Type'} = 'xlConditionValueHighestValue';
$colorScaleCriteria->{3}->{'FormatColor'}->{'Color'}=7039480;
$colorScaleCriteria->{3}->{'FormatColor'}->{'TintAndShade'}=0;
[download]