Conditional Formatting Excel VBA to AutoIT

[BJR]

Hello All,

I want to use Excel Conditional Formatting in AutoIt.

I would like to use this => Select Range -> Conditional Formatting -> Highlight Cell Rules -> Duplicate Values

I don't know how to convert following VBA code to Excel, I searched a lot but couldn't find any solution:-

Code:

    Dim rg As Range
    Dim objCFUnique As UniqueValues
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = Range("A2", Range("A2").End(xlDown))
    
    objCFUnique = rg.FormatConditions.AddUniqueValues
    
    objCFUnique.DupeUnique = xlDuplicate
    
    objCFUnique.Interior.Color = vbRed

Code:

Above Code Doesn't Work.

How to use Conditional Formatting in AutoIT ?

 

Edited January 22, 2020 by BJR

[Subz]

Just record a macro in Excel and use that to build your script for example:

#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
    If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel_Temp.xlsx")
    If @error Then Exit

$oWorkbook.ActiveSheet.Usedrange.Columns("A:A").Select
$oExcel.Selection.FormatConditions.AddUniqueValues
$oExcel.Selection.FormatConditions($oExcel.Selection.FormatConditions.Count).SetFirstPriority
$oExcel.Selection.FormatConditions(1).DupeUnique = 1
With $oExcel.Selection.FormatConditions(1).Interior
    .PatternColorIndex = -4105
    .Color = 255
    .TintAndShade = 0
EndWith
    $oExcel.Selection.FormatConditions(1).StopIfTrue = False

 

Edited January 20, 2020 by Subz

[BJR]

1 hour ago, Subz said:

Just record a macro in Excel and use that to build your script for example:

#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
    If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel_Temp.xlsx")
    If @error Then Exit

Local $oSelection = $oWorkbook.ActiveSheet.Usedrange.Columns("A:A").Select
    $oExcel.Selection.FormatConditions.AddUniqueValues
    $oExcel.Selection.FormatConditions($oExcel.Selection.FormatConditions.Count).SetFirstPriority
    $oExcel.Selection.FormatConditions(1).DupeUnique = 1
    $oSelect = $oExcel.Selection.FormatConditions(1).Interior
    With $oExcel.Selection.FormatConditions(1).Interior
        .PatternColorIndex = -4105
        .Color = 255
        .TintAndShade = 0
    EndWith
    $oExcel.Selection.FormatConditions(1).StopIfTrue = False

 

Thanks for replying.

It is working.

[Subz]

np: Actually just updated the code above.

[BJR]

Now I am trying to color like this => Check attachment

Edited January 22, 2020 by BJR
Two Same Images

[water]

As Subz suggested: "Just record a macro in Excel and use that to build your script"

[BJR]

On 1/20/2020 at 6:39 PM, water said:

As Subz suggested: "Just record a macro in Excel and use that to build your script"

 

Thank You.

I will do it.

[BJR]

Final Conditional Formatting Code :-

Function:-

Func ConditionalFormatting($vWorksheet, $Range)

   $vWorksheet.Range($Range).FormatConditions.AddUniqueValues

   $vWorksheet.Range($Range).FormatConditions($vWorksheet.Range($Range).FormatConditions.Count).SetFirstPriority

   $vWorksheet.Range($Range).FormatConditions(1).DupeUnique = 1


   With $vWorksheet.Range($Range).FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
   EndWith


   With $vWorksheet.Range($Range).FormatConditions(1).Interior
        .PatternColorIndex = -4105 ;xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
     EndWith

   $vWorksheet.Range($Range).FormatConditions(1).StopIfTrue = False

EndFunc

Example:-

#include <Excel.au3>

    Local $ExcelFile = FileOpenDialog("Select Excel File For Conditional Formatting", "", "Excel Files (*.xls;*.xlsx)|All (*.*)", 3)

    If @error Or $ExcelFile = "" Then Exit


    Local $oExcel = _Excel_Open()
    
    Local $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile)
    
    
    ConditionalFormatting($vWorksheet,  "A1:A100")
    
    
    msgbox(-1, "", "")
    
    
    _Excel_BookClose($vWorksheet)
    
    _Excel_Close($oExcel)

 

Edited January 28, 2020 by BJR

[stick3r]

Hi, I have similar issue.
Recorder Macro to have Conditional formatting for Less than 0 numbers to be in red:

    Range("B12:D38").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

My AutoIt Code with integrated above macro:
 

#include <Excel.au3>

$oExcel = _Excel_Open()
Sleep(200)
$oWB = _Excel_BookAttach("Template.xlsx", "filename")

    $oExcel.Range("B12:D38").Select

    $oExcel.Selection.FormatConditions.Add(1, 6, "=""0""", "")

    $oExcel.Selection.FormatConditions($oExcel.Selection.FormatConditions.Count).SetFirstPriority

    With $oExcel.Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    EndWith
    $oExcel.Selection.FormatConditions(1).StopIfTrue = False

Problem is that AutoIt marks ALL numbers in red, not those less than 0. 
When recording macro and setting Conditional formatting manually, all works good and only less than 0 numbers are in red. 
Any ideas?

Thanks.

EDIT:
Found the issue in line: $oExcel.Selection.FormatConditions.Add(1, 6, "=0", "") 
There were too many quotation marks...

 

Edited May 29, 2020 by stick3r

[Subz]

The following works for me:

$oExcel.Selection.FormatConditions.Add(1, 6, "=0")