Excel Conditional Formatting

Get help with using AutoHotkey and its commands and hotkeys
User avatar
TheDewd
Posts: 1446
Joined: 19 Dec 2013, 11:16
Location: USA

Excel Conditional Formatting

Post by TheDewd » 09 Oct 2020, 14:21

I can't figure out how to convert this Excel macro to AutoHotkey COM Object code.

Home > Conditional Formatting > Highlight Cell Rules > Text That Contains... > Format cells that contain the text "PASS" with "Green Fill with Dark Green Text".

I want to add the condition to a specific column where the cell contents contain the text string "PASS".

Can someone assist me, please? Thank you!

Code: Select all

Sub Macro1()
    Selection.FormatConditions.Add Type:=xlTextString, String:="PASS", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

xlTextString := 9, xlContains := 0

I tried something like this to begin with:
XL.Range("J2:J4").FormatConditions.Add(9, 0, "PASS")


I know that I could use AutoHotkey to color the cells, but I'd like to use Excel's own conditional formatting if possible:

Code: Select all

Row := XL.Cells(XL.Rows.Count, 8).End(-4162).Row

For Cell In XL.Range("J2:J" Row) {
	If (XL.Range(Cell.Address).Text = "PASS") {
		XL.Range(Cell.Address).Font.Color := (0<<16) + (97<<8) + 0
		XL.Range(Cell.Address).Interior.Color := (206<<16) + (239<<8) + 198
	} Else If (XL.Range(Cell.Address).Text = "FAIL") {
		XL.Range(Cell.Address).Font.Color := (6<<16) + (0<<8) + 156
		XL.Range(Cell.Address).Interior.Color := (206<<16) + (199<<8) + 255
	}
}
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.

FanaticGuru
Posts: 1481
Joined: 30 Sep 2013, 22:25

Re: Excel Conditional Formatting

Post by FanaticGuru » 09 Oct 2020, 19:47

TheDewd wrote:
09 Oct 2020, 14:21
I can't figure out how to convert this Excel macro to AutoHotkey COM Object code.

Home > Conditional Formatting > Highlight Cell Rules > Text That Contains... > Format cells that contain the text "PASS" with "Green Fill with Dark Green Text".

I want to add the condition to a specific column where the cell contents contain the text string "PASS".

xlTextString := 9, xlContains := 0

I tried something like this to begin with:
XL.Range("J2:J4").FormatConditions.Add(9, 0, "PASS")

This code works and is close.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlRange := xlApp.ActiveSheet.Range("A1")
xlRange.FormatConditions.Add(1, 3, "PASS") ; xlCellValue := 1, xlEqual := 3, String
xlFC := xlRange.FormatConditions(1)
xlFC.Font.Color := -16752384
xlFC.Interior.Color := 13561798
This uses xlCellValue and xlEqual but I cannot get xlTextString := 9, xlContains := 0 to work either.

In theory changing the 1 and 3 to 9 and 0 should work but it is not that simple. Operator and TextOperator are not the same thing and I cannot figure out the parameter positions and format for passing TextOperator. My guess is it has to be passed as a ComObject but again I cannot figure out the exact data structure.

VBA allows for named parameters and it handles the data typing. With AutoHotkey all the positions and data types have to be manually done exactly right.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

User avatar
Xeo786
Posts: 290
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel Conditional Formatting

Post by Xeo786 » 10 Oct 2020, 03:30

found solution on following link https://stackoverflow.com/questions/27579771/how-to-create-text-contains-formattingconditional-format-condition-for-excel

but I was confuse how to define type.missing into AHK, I know "FormatConditions.Add(9,,,, "PASS", 0,,)" gona through error
so I just set them empty and it worked, I still do not know what are these field except these three we know now and where to put them :think:

Code: Select all

xlselection := ComObjActive("Excel.Application").Selection 
xlFC := xlselection.FormatConditions.Add(9, "", "", "", "PASS", 0, "","") 
xlFC.Font.Color := -16752384
xlFC.Interior.Color := 13561798
msgbox, %  "`nCel:" xlselection.address "`n Number of Conditions:" xlselection.FormatConditions.count
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

FanaticGuru
Posts: 1481
Joined: 30 Sep 2013, 22:25

Re: Excel Conditional Formatting

Post by FanaticGuru » 13 Oct 2020, 12:45

Xeo786 wrote:
10 Oct 2020, 03:30
found solution on following link https://stackoverflow.com/questions/27579771/how-to-create-text-contains-formattingconditional-format-condition-for-excel

but I was confuse how to define type.missing into AHK, I know "FormatConditions.Add(9,,,, "PASS", 0,,)" gona through error
so I just set them empty and it worked, I still do not know what are these field except these three we know now and where to put them :think:

Code: Select all

xlselection := ComObjActive("Excel.Application").Selection 
xlFC := xlselection.FormatConditions.Add(9, "", "", "", "PASS", 0, "","") 
xlFC.Font.Color := -16752384
xlFC.Interior.Color := 13561798
msgbox, %  "`nCel:" xlselection.address "`n Number of Conditions:" xlselection.FormatConditions.count

Nice find.

This is what I came up with for the parameter format:
; expression.Add (Type, Operator, Formula1, Formula2, TextOperator, ContainsOperator)
; expression represents a FormatConditions object


I was all around guessing that and it looks so obvious now as the documentations says expression.Add (Type, Operator, Formula1, Formula2) but how to pass the TextOperator and ContainsOperator is not documented anywhere I could find.

You don't really have to do all those "" null values. Just have to get the parameters in the correct positions.

Code: Select all

; expression.Add (Type, Operator, Formula1, Formula2, TextOperator, ContainsOperator)
; expression represents a FormatConditions object

xlApp := ComObjActive("Excel.Application")
xlRange := xlApp.ActiveSheet.Range("A1")
xlFC := xlRange.FormatConditions.Add(9,,,,"PASS",0)  ; xlTextString := 9, String, xlContain := 0
xlFC.Font.Color := -16752384
xlFC.Interior.Color := 13561798
This works for me.

Here are the other xlContainsOperator values:
2 xlBeginsWith
0 xlContains
1 xlDoesNotContain
3 xlEndsWith


FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

User avatar
Xeo786
Posts: 290
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel Conditional Formatting

Post by Xeo786 » 14 Oct 2020, 01:14

FanaticGuru wrote:
13 Oct 2020, 12:45
; expression.Add (Type, Operator, Formula1, Formula2, TextOperator, ContainsOperator)
; expression represents a FormatConditions object

FG
Thanks
This gonna help me :D
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Post Reply

Return to “Ask For Help”