Jump to content
Sign in to follow this  

Excel and Conditional Formatting

Recommended Posts



is it possible to set conditional formating to a new table?

And if yes - can someone show a short example?



Share this post

Link to post
Share on other sites

The easiest way (at least for me) is to create a macro with the formatting you need then use that VBA code and convert it to AutoIt.

Here's an example that writes numbers 1 through 10 in cells A1 to A10 then adds formatting to the entire range A1:A10 to make numbers 1-5 Bold and Green then makes numbers 6-10 Italic and Red.

#include <Excel.au3>

$oExcel = _ExcelBookNew()
$writeToColumn = 1

Global Const $xlCellValue = 1
Global Const $xlBetween = 1

For $i = 1 To 10
_ExcelWriteCell($oExcel, $i, $i, $writeToColumn) ;Write 1-10 in cells A1 - A10

With $oExcel
.Range("A1:A10").Select     ;Select A1:A10
.Selection.FormatConditions.Delete  ;Delete Existing FormatConditions
.Selection.FormatConditions.Add($xlCellValue, $xlBetween, "1", "5")  ;FormatConditions(1)
.Selection.FormatConditions.Add($xlCellValue, $xlBetween, "6", "10") ;FormatConditions(2)

With $oExcel.Selection.FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Font.ColorIndex = 3 ;Red

With $oExcel.Selection.FormatConditions(2)
.Font.Bold = False
.Font.Italic = True
.Font.ColorIndex = 10 ;Green

$oExcel = ""


Original VBA macro:

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="1", Formula2:="5"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 3
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="6", Formula2:="10"
    With Selection.FormatConditions(2).Font
        .Bold = False
        .Italic = True
        .ColorIndex = 10
    End With

Share this post

Link to post
Share on other sites

An update. I was not able to get the conditional formatting to use the correct relative reference for the comparison cell. I'd tell it =$B43, and the rule in the spreadsheet would be =$B4. I'd run it again and get different results. I didn't spot a pattern.

That was when I was adding data to an existing sheet, tacking my results on below any filled cells.

I switched to RC references in the comparison, and it worked.

Here's the final code snippet:

If $SAPDonglePosition Then

Local $endrow = UBound($arOutput)

With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$" & $startrow + 1 & ":" & "$" & $SAPDonglePosition & "$" & $startrow + $endrow - 1).FormatConditions.Add(1, 4, "=RC[-1]")

.Font.Colorindex = 30

.Interior.ColorIndex = 22



Share this post

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
Sign in to follow this  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.