Color the line when two dates are entered in the same line

[Romano_odK]

Good afternoon,

Is it possible with VBA to color a line say grey when two dates are entered in the same line. So in my case "Vkp verwerkingsdatum" and "Ikp verwerkingsdatum"? So with one date entered nothing happens. The line should stay in the range of the table.

Thank you for your time.

Ingangs datum	Meldings datum	Verkoop ingangsdatum	Leverancier	Productgroep	Verhoging inkoop %	Verhoging verkoop %	Vkp verwerkingsdatum	Ikp verwerkingsdatum	Verwerker	Notitie
10/08/2022	31/05/2022	08/01/2022	SVT	Brandwerend	1	8			Romano	Diverse verhogingen
01/07/2022	12/05/2022	01/07/2022	Coroplast		2	9			Romano	Verhoging XML VKP KP IKP zijn gepland
01/07/2022	25/05/2022	01/07/2022	CPG	divers - zie prl G-schijf	3	6			Freek
01/07/2022	27/05/2022		Sievert		6	8
01/06/2022	01/06/2022	01/06/2022	Duco	Roosters	1	9			Freek
01/06/2022	02/05/2022	01/06/2022	Vito	PE celband/Vitoflex afstandh/Vitoseal	2	6			Shirley
01/06/2022	02/05/2022	01/06/2022	Vito	Vitomount/Glazingmount/Vito BG1-2/Vitofix	3	8			Shirley
01/05/2022	21/04/2022	01/06/2022	TPP	Rubber	6	9			Shirley
01/05/2022	22/04/2022		Stokvis	tapes	1	6			Freek
01/05/2022	22/04/2022	01/05/2022	Castelein	Dow Sil	2	8			Freek	inkoop nog aanpassen
01/05/2022	29/04/2022	01/05/2022	Stokvis	alles	3	9
01/05/2022	31/03/2022	16/05/2022	Bostik - Den Braven	Hybriden/MSP	6	6			Freek	inkoop nog verwerken
01/05/2022	31/03/2022	16/05/2022	Bostik - Den Braven	Vawa MSP	1	8			Freek	inkoop nog verwerken
01/05/2022	31/03/2022	16/05/2022	Bostik - Den Braven	PUR schuim	2	9			Freek	inkoop nog verwerken
01/05/2022	31/03/2022	16/05/2022	Bostik - Den Braven	alles (m.u.v. Siliconen)	3	6			Freek	inkoop nog verwerken
18/04/2022	06/04/2022	01/05/2022	Adego	PU/PE Rondschuim	6	8			Shirley
11/04/2022	31/03/2022		GSH	Kunststof	1	9			Romano
01/04/2022	01/02/2022		CPG	divers - zie lijst	2	6
01/04/2022	17/02/2022		Rivafoam	schuim PE/PU	3	8			Freek
01/04/2022	22/03/2022	01/04/2022	Sika	alles	6	9			Joshua
21/03/2022	25/02/2022		Bloem	divers - zie G schijf	1	6			Freek
14/03/2022	18/02/2022		Decotric		2	8			Romano
11/03/2022	11/03/2022	11/03/2022	Duco/GKL/AR	Duco roosters	3	9			Freek
01/03/2022	16/01/2022	01/02/2022	Coroplast	AWX	6	6			Romano	Alles verwerkt
14/02/2022	14/02/2022	21/02/2022	Fenoplast	divers - grondstoftoeslag	1	8			Freek
01/02/2022	04/11/2022	21/12/2022	Henkel	MS	2	9			Freek
01/02/2022	01/02/2022		Otto Chemie	S (siliconen)	3	6			Freek
01/02/2022	13/12/2022		Otto Chemie	M (hybride)	6	8			Freek
01/02/2022	17/01/2022		CPG	prijslijst per 1/feb - zie G schijf	1	9			Freek
01/02/2022	19/02/2022	19/01/2022	Sika	alles	2	6			Joshua
01/02/2022	21/02/2022	01/05/2022	Impa	Woodfill/All-Fill/Plastuk/Flexipox	3	8			Shirley	vooraankondiging 23-12-2021
01/02/2022	21/02/2022	01/05/2022	Impa	Compa	6	9			Shirley	vooraankondiging 23-12-2021
01/02/2022	21/02/2022	01/05/2022	Impa	Doseerapparaten Woodfill+All-Fill	1	6			Shirley	vooraankondiging 23-12-2021
17/01/2022	15/12/2021		Connect	acrylaat	2	8			Freek	verkoopprijs checken
17/01/2022	15/12/2021		Connect	silicone	3	9			Freek	verkoopprijs checken
17/01/2022	15/12/2021		Connect	MSP/Hybride	6	6			Freek	verkoopprijs checken
17/01/2022	15/12/2021		Connect	PU schuim	1	8			Freek	verkoopprijs checken
02/01/2022	02/01/2022		Bloem	divers - zie G schijf	2	9			Freek
01/01/2022	01/01/2022	28/02/2022	Vosschemie	Woodcap repairs+polyester	3	6			Romano

 

[Anthony47]

Try the following macro, to be loaded into the vba class module of the working sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbName As String, myC As Range, ColA As Object, ColB As Object
Dim myOff As Long
'
tbName = "TabellaOne"                                                           '<<< Name of the Table
Set ColA = Me.ListObjects(tbName).ListColumns("Vkp verwerkingsdatum").Range     '<<< Name of the Header 1
Set ColB = Me.ListObjects(tbName).ListColumns("Ikp verwerkingsdatum").Range     '<<< Name of the Header 2
'
For Each myC In Target
    If Not Application.Intersect(ColA, myC) Is Nothing Then
        myOff = 1
    ElseIf Not Application.Intersect(ColB, myC) Is Nothing Then
        myOff = -1
    Else
        myOff = 0
    End If
    If myOff <> 0 Then
        If IsDate(myC.Value) And IsDate(myC.Offset(0, myOff).Value) Then
            Application.Intersect(myC.EntireRow, Me.ListObjects(tbName).DataBodyRange).Interior.Color = RGB(200, 200, 200)
        Else
            Application.Intersect(myC.EntireRow, Me.ListObjects(tbName).DataBodyRange).Interior.Color = xlNone
        End If
    End If
Next myC
End Sub

To properly set the code:
-rightclick on the tab with the name of the sheet that contains the table; select Display code; this will open the vba editor at the right page
-copy the code and paste it into the empty frame
The 3 lines market <<< need to be customized with your data

Then return to excel and try typing or remiving dates into the "verwerkingsdatum" columns

 

[igold]

Another way... Again this should be loaded in the worksheet module of the worksheet where the table resides. Additionally, I used Sheet1 and Table1 as your object names- please change to fit your needs...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim tbl As ListObject, i As Long
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    
    Application.ScreenUpdating = False
    Set tbl = ws1.ListObjects("Table1")
    If Intersect(Target, ws1.ListObjects("Table1").DataBodyRange) Is Nothing Then Exit Sub
    For i = 1 To tbl.ListRows.Count
        tbl.ListRows(i).Range.Interior.ColorIndex = 0
        If IsDate(tbl.DataBodyRange(i, 8)) And IsDate(tbl.DataBodyRange(i, 9)) Then
            tbl.ListRows(i).Range.Interior.ColorIndex = 15
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub