BJR Posted January 20, 2020 Posted January 20, 2020 (edited) 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 BJR
Subz Posted January 20, 2020 Posted January 20, 2020 (edited) 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
BJR Posted January 20, 2020 Author Posted January 20, 2020 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. BJR
BJR Posted January 20, 2020 Author Posted January 20, 2020 (edited) Now I am trying to color like this => Check attachment Edited January 22, 2020 by BJR Two Same Images BJR
water Posted January 20, 2020 Posted January 20, 2020 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.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 PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
BJR Posted January 22, 2020 Author Posted January 22, 2020 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
BJR Posted January 28, 2020 Author Posted January 28, 2020 (edited) 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 BJR
stick3r Posted May 29, 2020 Posted May 29, 2020 (edited) 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 Posted May 29, 2020 Posted May 29, 2020 The following works for me: $oExcel.Selection.FormatConditions.Add(1, 6, "=0") Danyfirex and stick3r 2
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now