Reference to ...ListColumns("<name>"").index in Conditional Formatting Formula

Hans Troost · Aug 30, 2023

3 years ago as beginner VBA programmer I developed an application for the Food Bank: a "hand-out-list" for food-packages based on client-data in our "dossier"-system.
Data extracted with Power Query and "enriched" with a VBA-script to the resulting "hand-out-list".

List size about 730 rows, example of 18 rows of fake data:
The Power Query:

VBnuHighlight.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

VB.nu

W/D/V

L.U.

V

K

PP

KW

Speciaal

Pakket

Aanh.

Naam

Straat

Aanw

Bez.

Leeg

PC

Woonplaats

Niet geweest

Afgemeld

datumstopzetting

redenstopzetting

Opmerking

Jongste

Tel1

Tel2

Nieuw

Gestopt

3W

Sinds

JJ

2

646

1

2

de heer

AA A

646

0

26-8-2023

3

621

W

25-2-2024

2

3

4

familie

Amersfoorttest1 H

Teststraat1 1

3811AA

0

1

2

0

2

4

622

W

1-12-2023

2

4

familie

Amersfoorttest2 T2

Teststraat2 2

3811AB

23

0611111110

622

0

26-8-2023

5

609

24-4-2022

1

2

1

de heer

ASolutions_Achternaam

17-02 28-11

0

609

0

26-8-2023

6

578

V

30-12-2023

1

6

mevrouw

Boer de

Pakketweg 4

1234AA

Ergens

0

7

579

V

1-8-2022

1

8

GR + BL

halal

de heer

Far

VBAweg 15

1234TT

Amersfoort

1

579

0

26-8-2023

1

8

677

D

28-2-2024

2

4

familie

Gerritsen H

Troostweg 4

3456AA

Amersfoort

2

033436789

0651234798

0

2

9

577

W

13-12-2023

1

12

GR + GR + BL

halal

mevrouw

Jansen | SSP pas + sport rugtas T

Argonweg 10

1234AA

Amersfoort

10-03

577

0

26-8-2023

10

584

W

16-9-2021

1

8

GR + BL

de heer

Janssen ! 21aug LAATSTE KEI PAKKET

Voedselstraat 22

1234BB

Nergenshuizen

46

584

0

26-8-2023

11

674

M

13-8-2023

1

KEI

de heer

Janssen J

0612222222

674

0

26-8-2023

12

582

V

5-12-2023

1

5

halal

de heer

Jonge ! bel Tst.31 de

Uraniumweg 21

1234AA

Leusden

06-04

0

13

600

V

13-2-2024

1

2

de heer

Klaassen

11

600

0

26-8-2023

1

14

583

W

3-1-2024

1

11

GR + GR + WI

de heer

Klaassen

Keistraat 14

1234CC

Amersfoort

0

15

605

W

21-2-2024

1

5

halal

de heer

Peters (collega)

0

16

585

W

19-1-2024

1

4

de heer

Pieters

Wederik 21

3831AW

Leusden

06-04

2

585

0

26-8-2023

2

17

535

W

13-3-2022

2

4

halal

familie

Pietersen | Cieka heeft een vraag G

1

535

0

26-8-2023

4

18

653

1

de heer

TEst ICT

653

0

26-8-2023

19

538

22-2-2024

2

3

familie

Tëst_1

Teststraat 1

3000AA

2

0

2

Uitgiftelijst

And the resulting "hand-out list"

2023-08-30.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

VB.nu

W/D/V

L.U.

V

K

PP

KW

Speciaal

Pakket

Aanh.

Naam

Straat

Aanw

Bez.

Leeg

PC

Woonplaats

Niet geweest

Afgemeld

datumstopzetting

redenstopzetting

Opmerking

Tel1

Tel2

Nieuw

Sinds

2

646

1

2

de heer

AA A

646

26-08-2023

3

621

W

25-02-2024

2

3

4

familie

Amersfoorttest1 H

Teststraat1 1

3811AA

1

2

4

622

W

01-12-2023

2

4

familie

Amersfoorttest2 T2

Teststraat2 2

3811AB

0611111110

622

26-08-2023

5

609

24-04-2022

1

2

1

de heer

ASolutions_Achternaam

17-02 28-11

609

26-08-2023

6

578

V

30-12-2023

1

6

mevrouw

Boer de

Pakketweg 4

1234AA

Ergens

7

579

V

01-08-2022

1

8

GR + BL

halal

de heer

Far

VBAweg 15

1234TT

Amersfoort

579

26-08-2023

8

677

D

28-02-2024

2

4

familie

Gerritsen H

Troostweg 4

3456AA

Amersfoort

033436789

0651234798

9

577

W

13-12-2023

1

12

GR + GR + BL

halal

mevrouw

Jansen | SSP pas + sport rugtas T

Argonweg 10

1234AA

Amersfoort

10-03

577

26-08-2023

10

584

W

16-09-2021

1

8

GR + BL

de heer

Janssen ! 21aug LAATSTE KEI PAKKET

Voedselstraat 22

1234BB

Nergenshuizen

584

26-08-2023

11

674

M

13-08-2023

1

KEI

de heer

Janssen J

0612222222

674

26-08-2023

12

582

V

05-12-2023

1

5

halal

de heer

Jonge ! bel Tst.31 de

Uraniumweg 21

1234AA

Leusden

06-04

13

583

W

03-01-2024

1

11

GR + GR + WI

de heer

Klaassen

Keistraat 14

1234CC

Amersfoort

14

600

V

13-02-2024

1

2

de heer

Klaassen

600

26-08-2023

15

605

W

21-02-2024

1

5

halal

de heer

Peters (collega)

16

585

W

19-01-2024

1

4

de heer

Pieters

Wederik 21

3831AW

Leusden

06-04

585

26-08-2023

17

535

W

13-03-2022

2

4

halal

familie

Pietersen | Cieka heeft een vraag G

535

26-08-2023

18

653

1

de heer

TEst ICT

653

26-08-2023

19

538

22-02-2024

2

3

familie

Tëst_1

Teststraat 1

3000AA

30-08 totaal

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K19	Cell Value	contains " ! "	text	YES
K2:K19	Cell Value	contains " \| "	text	YES

For a lot of columns I started with Conditional Formatting of data, often bases on values in other columns from de Power Query - which I delete afterwards, Deletion is possible because I wrote a small sub to replace the conditional format with permanent fillcolor

Now business' requirements change and new columns are coming, some are shifting to other positions and others will be deleted. And the code I wrote uses fixed column numbers in the conditional formatting, like this: .FormatConditions.Add Type:=xlExpression, Formula1:="=[B]$A2=$Z2[/B]".
But I did not succeed in getting it working

I now need more flexible code, just using column names to refer to them. like .ListColumns("<name>").index.

Here is the code I used, for the 1st (left most) column VB.nu: not for the column Name, which still has the conditional format.:

VBA Code:

Sub VBnuHighlight()
'Stripped the code as as much as possible

    Dim tTbl As ListObject 'Target Table
    Dim rFiltered As Range ' gefilterde regels
    Dim rRecord As Range ' specifieke regel uit gefilterde regel

    Set tTbl = ThisWorkbook.Sheets(1).ListObjects("Uitgiftelijst") 'adapted - reduced code - for MrExcel posting

    With tTbl.ListColumns("VB.nu").DataBodyRange
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
    
'
' worked for years, replacing the $A2=$Z2 with liscolum-names based version did not work. See some attempts below.
        .FormatConditions.Add Type:=xlExpression, Formula1:="=[B]$A2=$Z2[/B]"
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=ttbl.listcolumns(""VB.nu"").value=ttbl.listcolumns(""Nieuw"").value"
'        .FormatConditions.Add Type:=xlExpression, Formula1:=""=RC" & tTbl.ListColumns("VB.nu").Index & "=RC" & tTbl.ListColumns("Nieuw").Index & """"
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC1=RC26"
'        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Color = vbBlue
            .TintAndShade = 0.75
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    tTbl.AutoFilter.ShowAllData
    RemoveCF tTbl.ListColumns("VB.nu").DataBodyRange 'zet Cond. Format om in permanente vulkleur.
End Sub

and the RemoveCF routine:

VBA Code:

Sub RemoveCF(ByRef mySel As Range) ' vervang conditional format door permanent format MySel = My Selection

    Dim myCell As Range
    
    For Each myCell In mySel
        myCell.Interior.Color = myCell.DisplayFormat.Interior.Color
        myCell.Font.Color = myCell.DisplayFormat.Font.Color
    Next myCell

    mySel.FormatConditions.Delete

End Sub

The only thing I could achieve, for me is a poor-man's-solution: using another mechanism including looping over regarding records:

VBA Code:

Sub VBnuColorPoor()
'
' a "poor-man's-solution" implemented:
    Dim tSh As Worksheet ' Target Worksheet
    Dim tTbl As ListObject 'Target Table
    Dim rFiltered As Range ' gefilterde regels
    Dim rRecord As Range ' specifieke regel uit gefilterde regel

    Set tSh = ThisWorkbook.ActiveSheet
    Set tTbl = ThisWorkbook.Sheets(1).ListObjects("Uitgiftelijst") 'adapted - reduced code - for MrExcel posting

With tTbl.DataBodyRange
    .AutoFilter field:=tTbl.ListColumns("Nieuw").Index, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>"""
    Set rFiltered = Nothing
    Set rRecord = Nothing
    On Error Resume Next ' voor als er geen Gemaakte Passen zouden zijn: komt in principe niet voor
        Set rFiltered = .SpecialCells(xlCellTypeVisible).EntireRow
    On Error GoTo 0
    If rFiltered Is Nothing Then
    Else
        For Each rRecord In rFiltered.Rows
            If tSh.Cells(rRecord.Row, tTbl.ListColumns("VB.nu").Index).Value = tSh.Cells(rRecord.Row, tTbl.ListColumns("Nieuw").Index).Value Then
                With tSh.Cells(rRecord.Row, tTbl.ListColumns("VB.nu").Index).Interior
                    .Color = vbBlue
                    .TintAndShade = 0.75
                End With
            End If
        Next rRecord
    End If
End With
tTbl.AutoFilter.ShowAllData

End Sub

So my question is:
Is it possible do use references to ListColumn-names in stead of column numbers/alphabet char's in the Cond. Format formula's ?

Wish (not a requirement):- no loop over all table records.

Please keep in mind: I have to do this (as I currently do) with a lot of columds in this sheets.

I hope that someone can help me with this. Thank you for at least reading my post!

Kind regards, Hans Troost

Hans Troost · Aug 30, 2023

My apologies: I ruined the "bolded" 1st example code bij "bolding"the cell reference, so making it un-runnable (is this English?).
So please find below the right code, which can be runned:

VBA Code:

Sub VBnuHighlight()
'Stripped the code as as much as possible

    Dim tTbl As ListObject 'Target Table
    Dim rFiltered As Range ' gefilterde regels
    Dim rRecord As Range ' specifieke regel uit gefilterde regel

    Set tTbl = ThisWorkbook.Sheets(1).ListObjects("Uitgiftelijst") 'adapted - reduced code - for MrExcel posting

    With tTbl.ListColumns("VB.nu").DataBodyRange
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
    
'
' worked for years, replacing the $A2=$Z2 with liscolum-names based version did not work. See some attempts below.
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=$Z2"
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=ttbl.listcolumns(""VB.nu"").value=ttbl.listcolumns(""Nieuw"").value"
'        .FormatConditions.Add Type:=xlExpression, Formula1:=""=RC" & tTbl.ListColumns("VB.nu").Index & "=RC" & tTbl.ListColumns("Nieuw").Index & """"
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC1=RC26"
'        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Color = vbBlue
            .TintAndShade = 0.75
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    tTbl.AutoFilter.ShowAllData
    RemoveCF tTbl.ListColumns("VB.nu").DataBodyRange 'zet Cond. Format om in permanente vulkleur.
End Sub

Regards, Hans

Hans Troost · Aug 31, 2023

In the meanwhile I kept thinking and working on it and made some progress:

Using R1C1-style cell references I got it syntactically right, but is seems that I do not understand the R1C1 good enough.

I changed my code from the always working from
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=$Z2" to

.FormatConditions.Add Type:=xlExpression, Formula1:="=R[2]C" & tTbl.ListColumns("VB.nu").Index & "=R[2]C" & tTbl.ListColumns("Nieuw").Index & ""

So have the following code now

VBA Code:

Sub VBnuHighlight()
'Stripped the code as as much as possible

    Dim tTbl As ListObject 'Target Table

    Set tTbl = ThisWorkbook.Sheets(1).ListObjects("Uitgiftelijst") 'adapted - reduced code - for MrExcel posting

    With tTbl.ListColumns("VB.nu").DataBodyRange
'
' worked for years, replacing the $A2=$Z2 with liscolum-names based version did not work.
'
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=$Z2" 'Old, working code
        .FormatConditions.Add Type:=xlExpression, Formula1:="=R[2]C" & tTbl.ListColumns("VB.nu").Index & "=R[2]C" & tTbl.ListColumns("Nieuw").Index & ""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Color = vbBlue
            .TintAndShade = 0.75
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    tTbl.AutoFilter.ShowAllData
    RemoveCF tTbl.ListColumns("VB.nu").DataBodyRange 'zet Cond. Format om in permanente vulkleur.
End Sub

Sub RemoveCF(ByRef mySel As Range) ' trplace conditional format by permanent fill color. MySel = My Selection

    Dim myCell As Range
    
    For Each myCell In mySel
        myCell.Interior.Color = myCell.DisplayFormat.Interior.Color
        myCell.Font.Color = myCell.DisplayFormat.Font.Color
    Next myCell
    mySel.FormatConditions.Delete

End Sub

executed it - also using the debug immediate window - and got the following result:

VBnuHighlightSmall.xlsm

A

B

C

D

1

VB.nu

W/D/V

L.U.

Nieuw

2

646

3

621

W

25-2-2024

4

622

W

1-12-2023

622

5

609

24-4-2022

609

6

578

V

30-12-2023

7

579

V

1-8-2022

579

8

677

D

28-2-2024

9

577

W

13-12-2023

577

10

584

W

16-9-2021

584

11

674

M

13-8-2023

674

12

582

V

5-12-2023

13

600

V

13-2-2024

600

14

583

W

3-1-2024

15

605

W

21-2-2024

16

585

W

19-1-2024

585

17

535

W

13-3-2022

535

18

653

19

538

22-2-2024

Uitgiftelijst

and the immediate window - breakpoint at : With .FormatConditions(1).Interior shows:


? .FormatConditions.count
 1 
? .FormatConditions(1).formula1
=$A4=$D4
? ttbl.databodyrange.row
 2

What do I do wrong and where does the =$A4=$D4, this 4 originates from? The address should be =$A2=$Z2

Can someone explain me what is going on here?

Kind regards, Hans Troost

Hans Troost · Sep 1, 2023

I found the solution myself, so with future questions I will work better to solve, before posting questions. The solution:

Change to use R1C1-reference-style in the VBA-code in the right way and replace the original code line:
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=$Z2"
by

.FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C" & tTbl.ListColumns("VB.nu").Index & "=R[0]C" & tTbl.ListColumns("Nieuw").Index & ""

Most important is understanding relative R1C1-references, especially the use of R[0]Cn, since this cond. formatting is not used on cell by cell but on a whole ListObject("<something">.ListColumn("<name>") at once.

This did the trick and will make future maintenance work on my XL-VBA programs independent of column positions and order, by just referring to their names.

Regards, Hans Troost

Reference to ...ListColumns("<name>"").index in Conditional Formatting Formula

Hans Troost

New Member

Excel Facts

Hans Troost

New Member

Hans Troost

New Member

Hans Troost

New Member

Forum statistics

Share this page

Reference to ...ListColumns("<name>"").index in Conditional Formatting Formula

Hans Troost

New Member

Excel Facts

Hans Troost

New Member

Hans Troost

New Member

Hans Troost

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock