VBA Format Cell in range if its a Date and greater than row 9.

[Trebor200]

Hi All,

I have range starting at K9 that can extend down many rows and across many columns.

I would like an if statement to check if first its a date and then if the date itself is less than today or greater than the date in the same column on row 9 turn red.. example below..

Hoping someone could help... thanks.

 

[alansidman]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

 

[Trebor200]

TestFormat for X-Chart.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5
6
7
8
9										Start	25/03/2021	25/03/2021	03/09/2021	10/09/2021	17/09/2021	24/09/2021	01/10/2021	08/10/2021	15/10/2021
10
11											07/05/2021	07/05/2021	24/05/2021	24/05/2021	17/09/2021	17/09/2021	17/09/2021	17/09/2021	17/09/2021
12											x	x	11/05/2021	03/09/2021	03/09/2021	03/09/2021	03/09/2021	03/09/2021	18/10/2021
13											x	x	03/09/2021	10/09/2021	10/09/2021	10/09/2021	10/09/2021	08/10/2021	15/10/2021
14											x	x	s	21/09/2021	21/09/2021	21/09/2021	21/09/2021	21/09/2021	21/09/2021
15											x	x	s	s	21/09/2021	21/09/2021	21/09/2021	21/09/2021	21/09/2021
16											x	x	s	s	s	s	s	06/09/2021	06/09/2021
17											x	x	03/09/2021	10/09/2021	17/09/2021	24/09/2021	01/10/2021	08/10/2021	15/10/2021
18											x	x	15/07/2021	15/07/2021	17/09/2021	17/09/2021	17/09/2021	17/09/2021	17/09/2021
19											x	x	03/09/2021	10/09/2021	17/09/2021	24/09/2021	01/10/2021	08/10/2021	15/10/2021
20											x	x	20/08/2021	20/08/2021	16/09/2021	16/09/2021	16/09/2021	17/09/2021	17/09/2021
21											x	x	06/09/2021	06/09/2021	06/09/2021	24/09/2021	24/09/2021	24/09/2021	24/09/2021
22											x	x	03/09/2021	10/09/2021	17/09/2021	24/09/2021	01/10/2021	08/10/2021	15/10/2021
23											x	x	s	s	s	s	s	s	s
24											x	x	s	s	s	s	s	s	s
25											x	x	05/08/2021	05/08/2021	05/08/2021	05/08/2021	05/08/2021	05/08/2021	05/08/2021
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L11	Cell Value	>L$9	text	NO
M25:S25	Cell Value	>M$9	text	NO
M17:Q22	Cell Value	>M$9	text	NO
R16:S22	Cell Value	>R$9	text	NO
O15:S15	Cell Value	>O$9	text	NO
M11:S11	Cell Value	>M$9	text	NO
N14:S14	Cell Value	>N$9	text	NO
K11	Cell Value	>K$9	text	NO
M12:S13	Cell Value	>M$9	text	NO

 

[Trebor200]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Hi Thanks for reply i have added i have manually used conditional format to show example.

 

[alansidman]

Option Explicit

Sub Foo()
    Dim rng As Range
    Dim c As Range
    Dim lc As Long, lr As Long, cCol As Long
    lc = Cells(9, Columns.Count).End(xlToLeft).Column
    lr = Range("K" & Rows.Count).End(xlUp).Row
    Set rng = Range(Cells(9, 11), Cells(lr, lc))
    For Each c In rng
        If IsDate(c) Then
            cCol = c.Column
            If c > Cells(9, cCol) Then
                c.Interior.ColorIndex = 38
                c.Font.ColorIndex = 3
            End If
        End If
    Next c
    MsgBox "completed"

End Sub

 

[Trebor200]

Option Explicit

Sub Foo()
    Dim rng As Range
    Dim c As Range
    Dim lc As Long, lr As Long, cCol As Long
    lc = Cells(9, Columns.Count).End(xlToLeft).Column
    lr = Range("K" & Rows.Count).End(xlUp).Row
    Set rng = Range(Cells(9, 11), Cells(lr, lc))
    For Each c In rng
        If IsDate(c) Then
            cCol = c.Column
            If c > Cells(9, cCol) Then
                c.Interior.ColorIndex = 38
                c.Font.ColorIndex = 3
            End If
        End If
    Next c
    MsgBox "completed"

End Sub

Excellent worked a treat Thank You

 

[alansidman]

You are welcome.