CONDITIONAL FORMAT op Tabel dmv VBA

[SjofaaSj]

Een TABEL met 6 kolommen wordt op verschillende sheets (onder een andere naam) herhaald.
Op elk van die tabellen wil ik een voorwaardelijke opmaak plaatsen:
zodra het woord 'NiL' voorkomt, moet de ganse tabelrij rood kleuren.
Dat woord kan voorkomen in de tabelkolommen 'Titel' of 'Prijs'.

Onderstaande code heb ik al, maar als ik de kolomtitel wil gebruiken ipv de celverwijzingen,
krijg ik de error 'syntaxisfout' (test1) of 'ongeldige procedureaanroep" (test2)

Sub TableConditionalFormat()

    Dim Rng As Range
    Dim myCell As Excel.Range
    Dim myTbl As Excel.ListObject
    Dim myRow As Long

'set parameters
    Set myTbl = ActiveSheet.ListObjects(1)
    Set myCell = ActiveCell
    Set myTbl = myCell.ListObject
    myCnt = myTbl.ListRows.Count
    Set Rng = myTbl.DataBodyRange
    Rng.Select
'define Cond Format
    Selection.FormatConditions.Delete
'macrorecorder
'    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
'        "=OF($D17=""NiL"";$F17=""NiL"")"
'test1
[COLOR="#FF0000"]'    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
'         "=OF(" & myTbl[Titel] & "=""NiL"";" & myTbl[Prijs] & "=""NiL"")"[/COLOR]
'test2
[COLOR="#FF0000"]    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
         "=OF(" & myTbl & "[Titel]=""NiL"";" & myTbl & "[Prijs]=""NiL"")"[/COLOR]

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 8420607
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Waar zit het addertje onder het gras?

SjofaaSj

 

[OctaFish]

Formules moeten volgens mij altijd in het Engels zijn. Dus geen OF, maar OR.

 

[SjofaaSj]

Dat dacht ik ook, maar de oorspronkelijke versie is opgenomen met de macro-recorder en ook daar staat 'OF' (ik heb een NL Excelversie).
Zonet nog de proef op de som genomen en de foutboodschap is niet veranderd, dus vermoed ik dat het niet daaraan ligt.

 

[Leotaxi]

denk niet dat myTb1 herkend wordt in de formule die geplaatst wordt

je kan ook zoiets achter het werkblad zetten

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Or Target.Column = 6 Then
    If Target.Value = "Nil" Then
        'aanpassen naar juiste bereik
        With Range("A" & Target.Row, "F" & Target.Row).Interior
            .Color = 8420607
        End With
    End If
    If Range("D" & Target.Row).Value <> "Nil" And Range("F" & Target.Row).Value <> "Nil" Then
        'aanpassen naar juiste bereik
        With Range("A" & Target.Row, "F" & Target.Row).Interior
            .Pattern = xlNone
        End With
    End If
End If
End Sub

mvg
Leo

 

[SjofaaSj]

Leotaxi

Een gelijkaardige verwijzing naar de tabel gebruik ik in een formule die elders op het werkblad staat (dus niet in de Tabel):

 Range("GSpecialBrut").FormulaR1C1 = "=SUM(" & myTbl & "[Totaal])"

Daar lukt het wel, dus zou mi. ook bij Voorwaardelijke opmaak moeten lukken
... alleen weet ik niet precies hoe en ik denk dat het puur een kwestie van syntax is.

Omdat de kans bestaat dat de tabel niet overal op dezelfde plaats staat, wil ik liever geen gebruik maken van vaste celverwijzingen.
Met de de tabelnaam en kolomtitels ben ik zeker dat het op de juiste cellen slaat.

 

[Leotaxi]

in deze kan de tabel gelijk waar op het blad staan, in de
code wordt wel gebruikt gemaakt van de offset functie, die dien
je aan te passen naargelang de kolommen voor en kolommen na Prijs en Titel


mvg
Leo

 

[SjofaaSj]

Leotaxi

Goed gevonden... al heb ik je code niet integraal gebruikt, want

1. met een "worksheet change event" verlies je de "UNDO" functionaliteit
2. de "conditional format macro" kan ik in een gewone module zetten waarnaar ik ook vanuit andere sheets kan verwijzen

Maar... je suggestie bracht me wel op een idee en ik heb wel een deel ervan gebruikt.
Omdat die 'conditional format' niet met tabelnamen maar wel met celverwijzingen werkt,
zoek ik de cel-adressen op die bij de naam horen en gebruik ik die in de formule.

Misschien een beetje omslachtig, maar het werkt probleemloos.
Dit is dan de finale versie geworden:

Sub CondiForm()

    Dim Rng As Range
    Dim myTbl As Excel.ListObject
    Dim myRow As Long
    Dim myTit As Integer, myPri As Integer
    Dim celTit As String, celPri As String
'set parameters
    Set myTbl = ActiveSheet.ListObjects(1)
    Set Rng = myTbl.DataBodyRange
    myRow = myTbl.HeaderRowRange.Row
    myTit = Cells.Find(What:="Titel", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    myPri = Cells.Find(What:="Prijs", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    celTit = Cells(myRow + 1, myTit).Address(RowAbsolute:=False)
    celPri = Cells(myRow + 1, myPri).Address(RowAbsolute:=False)
'define Cond Format
    Rng.FormatConditions.Delete
    Rng.FormatConditions.Add Type:=xlExpression, Formula1:="=OF(" & celTit & "=""NiL"";" & celPri & "=""NiL"")"
    Rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 8420607
        .TintAndShade = 0
    End With
    Rng.FormatConditions(1).StopIfTrue = False
End Sub

Dus bedankt voor je input! :thumb:

 

[SjofaaSj]

for what it's worth:
een alternatieve versie die eenvoudiger is en elke aanwezigheid van "NiL" opmerkt in de ganse tabel,
ongeacht de kolomnaam (dus ook geen fout als de kolomnaam niet in de tabel voorkomt!)

Sub CondiForm2()

    Dim myTbl As Excel.ListObject
    Dim myRng As Range
    Dim myRow1 As String
'set parameters
    Set myTbl = ActiveSheet.ListObjects(1)
    With myTbl
        Set myRng = .DataBodyRange
        myRow1 = .ListRows(1).Range.Address(RowAbsolute:=False)
    End With
'define Cond Format
    myRng.FormatConditions.Delete
    myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=AANTAL.ALS(" & myRow1 & ";""NiL"")<>0"
    'myRng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With myRng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 8420607
        .TintAndShade = 0
    End With
    myRng.FormatConditions(1).StopIfTrue = False
End Sub