Highlighting Passed Date and Another Cell is Blank

[Darlie247]

I know I can accomplish this using conditional formatting; however, there is/will be a lot of data so I wanted a VBA to do this.

What I am wanting to do is have the "PO# Due Date" in Column Q highlight red if it is past Today and Column W ("Received Date") is blank, and/or Column W is passed the date in Column Q. Does this make any sense?

Thanks ahead for any help or advice!

Ordering - Copy.xlsm
	P	Q		S	T	U	V	W
1
2
3
4	PO#	PO# DUE DATE		RECEIVED QUANTITY	RECEIVED UNIT	RECEIVED UNIT AMOUNT	RECEIVING TECH	RECEIVED DATE
5	1	24-Mar-22		20	Vial	-
6	2	24-Mar-22		30	Bottle	-	DAF
7	3	24-Mar-22		6	Each	-	DAF
8	4	24-Mar-22		6	Each	-	DAF
2022

Cells with Data Validation
Cell	Allow	Criteria
T5:T8	List	Each, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial

 

[kevin9999]

Does this give you what you want?

Darlie247.xlsx
	P	Q		S	T	U	V	W
1
2
3
4	PO#	PO# DUE DATE		RECEIVED QUANTITY	RECEIVED UNIT	RECEIVED UNIT AMOUNT	RECEIVING TECH	RECEIVED DATE
5	1	05-Apr-22		20	Vial	-
6	2	24-Mar-22		30	Bottle	-	DAF	04-Apr-22
7	3	05-Apr-22		6	Each	-	DAF	05-Apr-22
8	4	24-Mar-22		6	Each	-	DAF	06-Apr-22
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q5:Q8	Expression	=OR(AND(Q5>TODAY(),W5=""),W5>Q5)	text	NO

 

[Darlie247]

Does this give you what you want?

Darlie247.xlsx
	P	Q		S	T	U	V	W
1
2
3
4	PO#	PO# DUE DATE		RECEIVED QUANTITY	RECEIVED UNIT	RECEIVED UNIT AMOUNT	RECEIVING TECH	RECEIVED DATE
5	1	05-Apr-22		20	Vial	-
6	2	24-Mar-22		30	Bottle	-	DAF	04-Apr-22
7	3	05-Apr-22		6	Each	-	DAF	05-Apr-22
8	4	24-Mar-22		6	Each	-	DAF	06-Apr-22
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q5:Q8	Expression	=OR(AND(Q5>TODAY(),W5=""),W5>Q5)	text	NO

This is great! But would this slow down the workbook if I did this conditional formatting down the entire column of Q? Or is it simple/basic enough that excel could handle it?

Thanks for your help!!

 

[Darlie247]

Is it possible for a VBA to do this as well? I'm worried that conditional formatting might slow this workbook down...

 

[kevin9999]

Is it possible for a VBA to do this as well? I'm worried that conditional formatting might slow this workbook down...

I think you're right - I personally would also go for a VBA approach. There are 2 options

1. Run the macro yourself (put the following code in a standard module)

Option Explicit
Sub Darlie247()
    Dim rng As Range, c As Range
    Application.ScreenUpdating = False
    Set rng = Range("Q5:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
        For Each c In rng
            If (c.Value > Date And c.Offset(, 6) = "") Or (c.Offset(, 6) > c.Value) Then
                c.Font.Color = vbRed
                Else
                c.Font.ColorIndex = xlAutomatic
            End If
        Next c
    Application.ScreenUpdating = True
End Sub

2. Have the code run automatically whenever there's a change in columns Q or W (put the following code in the code area of the sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Q:Q,W:W"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim rng As Range, c As Range
        Set rng = Range("Q5:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
        For Each c In rng
            If (c.Value > Date And c.Offset(, 6) = "") Or (c.Offset(, 6) > c.Value) Then
                c.Font.Color = vbRed
                Else
                c.Font.ColorIndex = xlAutomatic
            End If
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

 

[Darlie247]

I think you're right - I personally would also go for a VBA approach. There are 2 options

1. Run the macro yourself (put the following code in a standard module)

Option Explicit
Sub Darlie247()
    Dim rng As Range, c As Range
    Application.ScreenUpdating = False
    Set rng = Range("Q5:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
        For Each c In rng
            If (c.Value > Date And c.Offset(, 6) = "") Or (c.Offset(, 6) > c.Value) Then
                c.Font.Color = vbRed
                Else
                c.Font.ColorIndex = xlAutomatic
            End If
        Next c
    Application.ScreenUpdating = True
End Sub

2. Have the code run automatically whenever there's a change in columns Q or W (put the following code in the code area of the sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Q:Q,W:W"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim rng As Range, c As Range
        Set rng = Range("Q5:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
        For Each c In rng
            If (c.Value > Date And c.Offset(, 6) = "") Or (c.Offset(, 6) > c.Value) Then
                c.Font.Color = vbRed
                Else
                c.Font.ColorIndex = xlAutomatic
            End If
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Thanks so much! Both of these work great!

 

[kevin9999]

Thanks so much! Both of these work great!

You're welcome and thanks for the feedback