Conditional Formatting (Excel) (Solved)

[Skeletor]

Hi Guys,

Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 

.Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A")
    .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets

I also tried it like this:

.Range("=$A3:$A4000").FormatConditions _
            .Add(xlCellValue, xlGreater, "=$A:$A")
    .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets

 

[FrancescoDiMuro]

@Skeletor
Could you please post the entire script or a reproducer, so we can see how we can help you?
And, since your script is using an external file ( the Excel one ), attach it or a reproducer of it.

Edited October 16, 2018 by FrancescoDiMuro

[Skeletor]

#include-once
#include <ExcelChart.au3>
.#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>



Global $aExcel = "C:\temp\test.xls"
Global $oWorkbook = _Excel_BookNew($aExcel)

With $oWorkbook.ActiveWorkbook.Sheets(1)
    .Range("A:M").Font.Color = 0x111111
    .Range("A:M").Font.Size = 9
    .Range("A:M").Font.Family = "Calibri"
    .Range("K1:M1").Interior.Color = 0xffCC99
    .Range("K2:M2").Interior.Color = 0xffCC99
    .Range("K1:M1").Borders.LineStyle = 1
    .Range("K2:M2").Borders.LineStyle = 1
    .Range("K2:M2").FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSets($xl3Arrows)

EndWith

 

Edited October 16, 2018 by Skeletor

[water]

Untested:

.Range("$A3:$A4000").FormatConditions.Add($xlCellValue, $xlGreater, "$A:$A")
.IconSet($xl3Arrows) ; ActiveWorkbook.iconsets

Details find here:
https://docs.microsoft.com/en-us/office/vba/api/excel.formatconditions.add

If the $xl* variables are undefined then you need to define them based on the MSDN enumerations.
https://docs.microsoft.com/en-us/office/vba/api/excel.xlformatconditiontype

Edited October 16, 2018 by water

[Skeletor]

Local $xlCellValue = 1
Local $xl3Arrows = 1

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.Add($xlCellValue, $xlGreater, "$A:$A")
    .IconSet($xl3Arrows) ;ActiveWorkbook.iconsets
EndWith

Although, this does work, its just abit of tweaking to get the icons to appear. 
But this is a start. 

@water Thanks for the assistance. Thanks for the Excel UDF's... really making the most out of these UDF's.

[water]

 

[Skeletor]

Hmmm, I got the icons to appear, but I cannot change them... even if I use Criteria(1) is still does not change to a different icon set. 

Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.AddIconSetCondition
    .IconSets($xl5Arrows)
EndWith

 

 

[Skeletor]

Not seen any changes with the iconset... 

Autoit script

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.AddIconSetCondition
    .Range("A3:A4000").FormatConditions.IconSet.IconSets = $xl3Arrows
    .Range("A3:A4000").FormatConditions.IconCriteria.Type = $xlConditionValueNumber
    .Range("A3:A4000").FormatConditions.IconCriteria.Value = 1

EndWith

	.Range("A3:A453").Select
	$Selection.FormatConditions.AddIconSetCondition
	$Selection.FormatConditions($Selection.FormatConditions.Count).SetFirstPriority
	With Selection.FormatConditions(1)
	.ReverseOrder = False
	.ShowIconOnly = False
	.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
	EndWith
	With .Selection.FormatConditions(1).IconCriteria(2)
	.Type = $xlConditionValuePercent
	.Value = 33
	.Operator = 7
	EndWith
	With Selection.FormatConditions(1).IconCriteria(3)
	.Type = $xlConditionValuePercent
	.Value = 67
	.Operator = 7
	EndWith

VB Script

[water]

I have never played with Iconset etc.
Can you please provide an example Excel workbook and a stripped down but fully functional script we can play with?
In addition a screenshot of the result you get and the result you expect would be great.

Plus information about the Excel version you run.

[Skeletor]

Hey water, sure let me compile this..give me a few minutes.

[Skeletor]

Here is the sample code:

expandcollapse popup

#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include-once
#include <ExcelChart.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)


_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "ID", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name", "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Company", "C1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Laptop", "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "SerialNumber", "E1")

For $i = 1 to 10 Step 1
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "00" & $i, "A" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name0" & $i, "B" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Scania", "C" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Dell", "D" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "W2314" & $i, "E" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=SUM(20 / " & $i & ")", "F" & $i)
Next


With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A:E").Font.Color = 0x111111
    .Range("A:E").Font.Size = 9
    .Range("A:M").Font.Family = "Arial"
    .Range("A1:F11").Borders.LineStyle = -4199

EndWith


; ######################################################################################################################

;                               ADD CONDITIONAL FORMATTING

; ######################################################################################################################

Local $xl3Arrows = 1
Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("F1:F11").FormatConditions.AddIconSetCondition
    .Range("F1:F11").FormatConditions.IconSet.IconSets = $xl3Arrows
    .Range("F1:F11").FormatConditions.IconCriteria.Type = $xlConditionValueNumber
    .Range("F1:F11").FormatConditions.IconCriteria.Value = 1

EndWith

and attached (Book5.xlsx) is the result including the expected result column.

 

Excel version - 365 (2016)

Book5.xlsx

[water]

Something like this (I know it is not 100% what you expect - but it runs without errors):

expandcollapse popup

#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include-once
; #include <ExcelChart.au3>

Local $oError = ObjEvent("AutoIt.Error", "_ErrFunc")
Global Const $xlDouble = -4119 ; Double line.

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "ID", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name", "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Company", "C1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Laptop", "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "SerialNumber", "E1")

For $i = 1 To 10 Step 1
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "00" & $i, "A" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name0" & $i, "B" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Scania", "C" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Dell", "D" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "W2314" & $i, "E" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=SUMME(20 / " & $i & ")", "F" & $i)
Next

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A:E").Font.Color = 0x111111
    .Range("A:E").Font.Size = 9
    .Range("A:M").Font.Name = "Arial"
    .Range("A1:F10").Borders.LineStyle = $xlDouble
EndWith

; ######################################################################################################################
;                               ADD CONDITIONAL FORMATTING
; ######################################################################################################################

Local $xl3Arrows = 1
Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

$oIconSet = $oExcel.ActiveWorkbook.Sheets(1).Range("F1:F10").FormatConditions.AddIconSetCondition
$oIconSet.IconSet = $oExcel.ActiveWorkbook.IconSets($xl3Arrows)
$oIconCriterion = $oIconSet.IconCriteria(2)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 15
$oIconCriterion.Operator = $xlGreaterEqual

; Retrieve information about each IconCriteria
For $oIconCriterion In $oIconSet.IconCriteria
    ConsoleWrite($oIconCriterion.Type & "-" & $oIconCriterion.Value & "-" & $oIconCriterion.Operator & @CRLF)
Next

Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

[Skeletor]

You hit the nail on the head. 
Thank you for this. I'm still amazed at how you came up with this. 

[water]

Just by checking MSDN (https://docs.microsoft.com/en-us/office/vba/api/excel.iconsetcondition) and following the examples I found there