Excel and Conditional Formatting

[Dizzy]

Hi,

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

And if yes - can someone show a short example?

Thanks

Dizzy

[MrMitchell]

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

[newsman220]

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

[AMFC]

For many months I have searched for the correct writing to be able to use a conditional formatting in an Excel, but looking for a condition on cells with text not with numbers.
After many tests, fortunately I discovered the secret yesterday.
The correct syntax is not the same as for numbers. In the case of text it is like this:

expandcollapse popup

;***********************************************************************************
; Ejemplo que rellena un rango con valores y luego le aplica un Formato Condicional
; siguiendo criterios de TEXTO.
; Autor: Antonio Miguel Fernandez
;***********************************************************************************
#include <Excel.au3>

; Que en los textos de las celdas se encuentre la letra J
$BUSCAR= "j"

; Crear el Objeto Excel y presentarlo al usuario
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

; Rellena el rango de celdas con distintos valores
For $i = 1 To 10 Step 1
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "00"&$i, "A"&$i)
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name0"&$i, "B"&$i)
   $Texto = Aleatorio()
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Texto, "C"&$i)
   $Texto = Aleatorio()
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Texto, "D"&$i)
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "10000"&$i, "E"&$i)
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=SUMA(A"&$i&";E"&$i&")", "F"&$i)
 Next

; Da formato al rango
Global Const $xlDouble = -4119      ; Borde de celdas con lineas dobles.
With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A:E").Font.Color = 0x111111
    .Range("A:E").Font.Size = 9
    .Range("A:M").Font.Name = "Arial"
    .Range("F1:F10").Borders.LineStyle = $xlDouble
EndWith


; Creando las condiciones a verificar.
$oExcel.Range("B1:D10").Select     ; Rango donde aplicar el formato.
;$oExcel.Selection.FormatConditions.Delete  ; Borrar los posibles formatos que hubiera en estas seleccion anteriormente.

; Formato condicional dependiendo del TEXTO que hay en las celdas.
Local Const $xlTextString=9               ;Cadena de texto
;--------------------------------------------------------
Local Const $xlBeginsWith=2               ;Begins with a specified value.
Local Const $xlContains=0                 ;Contains a specified value.
Local Const $xlDoesNotContain=1           ;Does not contain the specified value.
Local Const $xlEndsWith=3                 ;Endswith the specified value

;***************************************************************************
$oExcel.Selection.FormatConditions.Add($xlTextString,Default,Default,Default,$BUSCAR,$xlContains,Default,Default)
;***************************************************************************
$oExcel.Selection.FormatConditions($oExcel.Selection.FormatConditions.Count).SetFirstPriority
With $oExcel.Selection.FormatConditions(1).Font
   .Color = -16383844
   .TintAndShade = 0
EndWith
With $oExcel.Selection.FormatConditions(1).Interior
   .PatternColorIndex = -4105 ;xlAutomatic
   .Color = 13551615
   .TintAndShade = 0
EndWith
$oExcel.Selection.FormatConditions(1).StopIfTrue = False


Func Aleatorio()
   Local $sText = ""
   For $i = 1 To Random(5, 20, 1) ; Return an integer between 5 and 20 to determine the length of the string.
       $sText &= Chr(Random(65, 122, 1)) ; Return an integer between 65 and 122 which represent the ASCII characters between a (lower-case) to Z (upper-case).
   Next
   Return $sText
EndFunc