Jump to content

Conditional Formatting Excel VBA to AutoIT


Recommended Posts

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 by BJR

BJR

Link to post
Share on other sites

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 by Subz
Link to post
Share on other sites
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.

:thumbsup:

BJR

Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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 by BJR

BJR

Link to post
Share on other sites
  • 4 months later...

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 by stick3r
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...