set conditionalRange to range r of active sheet
set icObj to make new icon set format condition at conditionalRange with properties { format condition type: icon sets, condition operator: operator between, formula: "1" , formula2: "2", format condition priority: 1, reverse icon set order: false }
tell icon criteria 1 of icObj
set value to icon criterion index
set icon criterion icon to format condition icon gray up arrow
set icon criterion type to condition value number
set icon criterion value to 1
set icon criterion operator to operator greater equal
end tell
Excel got an error: Can’t make value of icon criteria 1 of icon set format condition 1 of range "L2:M35044" of active sheet into type string. (-1700)
This is the error I do receive, unable to switch the icon set, only the default condition is visible
I was able to do the same in windows with the following c# script
Excel.IconSetCondition IconCondition = range.FormatConditions.AddIconSetCondition();
IconCondition.IconSet = app.ActiveWorkbook.IconSets.Item[iconIndexList[iter]];
IconCondition.IconCriteria.Item[2].Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
IconCondition.IconCriteria.Item[2].Value = lowerrange[iter];
IconCondition.IconCriteria.Item[2].Operator = 7;
@Mockman sorry to pull you in do you have any idea on the same ?
That’s okay but I am not sure that I can help with this. Also, I’m not sure what your objective is. If you provide a few cells of data (that would make up range r) and what the intended outcome is then I’ll take a look.
I will say however, that the verbiage doesn’t make sense to me with icon criteria 1 since criteria is plural. If you’re going to use an index then it should be criterion, but I suspect that you’ll see an error with that as well.
Also, FWIW, the make command appears to work fine in that it puts up red/yellow/green traffic lights for a collection of data.
This is the output from the make command
I have to do this operation which makes a new rule for the icon set and have to choose a different icon set available in Excel
This is the output what I am expecting via the script
@Mockman
Try this:
tell application "Microsoft Excel"
set h to "H1:H5"
set cr to range h of active sheet
set isfc to icon set format condition 1 of cr
set fcis to format condition icon set of icon set format condition 1 of cr
get icon set id of fcis
--> icon set 3 traffic lights 1
-- to use elements of icon set 3 arrows gray
set icon criterion icon of icon criterion 1 of isfc to format condition icon gray down arrow
set icon criterion icon of icon criterion 2 of isfc to format condition icon gray side arrow
set icon criterion icon of icon criterion 3 of isfc to format condition icon gray up arrow
(* to use elements of icon set 3 traffic lights 1
set icon criterion icon of icon criterion 1 of isfc to format condition icon green circle
set icon criterion icon of icon criterion 2 of isfc to format condition icon yellow circle
set icon criterion icon of icon criterion 3 of isfc to format condition icon red circle
*)
-- however…
get icon set id of fcis
--> icon set custom
end tell
The above sets each individual element of the icon set which results in icon set custom instead of the intended icon set 3 traffic lights 1 or icon set 3 arrows gray
Note that there doesn’t seem to be a way to set the ‘icon set’, or maybe I’m just missing something obvious. Everything seemingly related to the set as a whole is read only. I’m running Excel 2011 so conceivably there have been updates here.
1 Like
Works as expected thanks.
Only change required here is it does operate on a defined conditional formatting range so,
make new format condition at cr
should be added after
set cr to range h of active sheet
Of course. By that time though, I’d made about 100 new format conditions and had stripped out every complication to try and figure out the syntax. Glad it works for you.
1 Like