Sign in to follow this  
Followers 0
Dizzy

Excel and Conditional Formatting

3 posts in this topic

Hi,

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

And if yes - can someone show a short example?

Thanks

Dizzy

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
Next

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)
EndWith

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

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

$oExcel = ""

Exit

Original VBA macro:

Range("A1:A10").Select
    Selection.FormatConditions.Delete
    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
    Range("A1:A1").Select

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

EndWith

EndIf

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  
Followers 0