Need to highlight cell with date

[MacroBB]

Hello all,

Can I get some help on how to use VBA to highlight cells in a daily report that have approximately 500 lines with multiple date columns? Only need to high light cells in Column B if it's equal to or later/bigger than Column A but earlier/smaller than any of the following column C to F? For example:

Date A	Date B (Column need to be highlighted)	Date C	Date D	Date E	Date F
Jan 10	Jan 9	Jan 9
Jan 10	Jan 11		Jan 12	Jan 12	Jan 13
Jan 15	Jan 15	Jan 9	Jan 15		Jan 13
Jan 15	Jan 16	Jan 15		Jan 16	Jan 17
Feb 1	Jan 31	Feb 1	Feb 1	Feb 1

What I would like to see:

Date A	Date B (Column need to be highlighted)	Date C	Date D	Date E	Date F
Jan 10	Jan 9	Jan 9
Jan 10	Jan 11		Jan 12	Jan 12	Jan 13
Jan 15	Jan 15	Jan 9	Jan 15		Jan 13
Jan 15	Jan 16	Jan 15		Jan 16
Feb 1	Jan 31	Feb 1	Feb 1	Feb 1

Thanks for your help!

 

[Micron]

As for code, this seems to work if the code is on the sheet you want to use it on and your columns are as shown. I elected to coerce date values to double data type in case your dates were strings. Not sure if it would have mattered.

Sub testRangeArray()
Dim ary As Variant
Dim Lrow As Long
Dim i As Integer, n As Integer, dblDate1 As Double, dblDate2 As Double
Dim blnGreater As Boolean

Lrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lrow
    blnGreater = False
    dblDate1 = Range("A" & i)
    dblDate2 = Range("A" & i).Offset(0, 1)
    If dblDate1 < dblDate2 Then
        ary = Range("C" & i & ":F" & i)
        For n = 1 To UBound(ary, 2)
            If dblDate2 < CDbl(ary(1, n)) And WorksheetFunction.IsNumber(CDbl(ary(1, n))) Then
                blnGreater = True
                 Exit For
            End If
        Next
    End If
    If blnGreater = True Then Range("A" & i).Offset(0, 1).Font.Color = vbRed
Next

End Sub

Result:

 

[MacroBB]

As for code, this seems to work if the code is on the sheet you want to use it on and your columns are as shown. I elected to coerce date values to double data type in case your dates were strings. Not sure if it would have mattered.

Sub testRangeArray()
Dim ary As Variant
Dim Lrow As Long
Dim i As Integer, n As Integer, dblDate1 As Double, dblDate2 As Double
Dim blnGreater As Boolean

Lrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lrow
    blnGreater = False
    dblDate1 = Range("A" & i)
    dblDate2 = Range("A" & i).Offset(0, 1)
    If dblDate1 < dblDate2 Then
        ary = Range("C" & i & ":F" & i)
        For n = 1 To UBound(ary, 2)
            If dblDate2 < CDbl(ary(1, n)) And WorksheetFunction.IsNumber(CDbl(ary(1, n))) Then
                blnGreater = True
                 Exit For
            End If
        Next
    End If
    If blnGreater = True Then Range("A" & i).Offset(0, 1).Font.Color = vbRed
Next

End Sub

Result:
View attachment 87965

 

[MacroBB]

As for code, this seems to work if the code is on the sheet you want to use it on and your columns are as shown. I elected to coerce date values to double data type in case your dates were strings. Not sure if it would have mattered.

Sub testRangeArray()
Dim ary As Variant
Dim Lrow As Long
Dim i As Integer, n As Integer, dblDate1 As Double, dblDate2 As Double
Dim blnGreater As Boolean

Lrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lrow
    blnGreater = False
    dblDate1 = Range("A" & i)
    dblDate2 = Range("A" & i).Offset(0, 1)
    If dblDate1 < dblDate2 Then
        ary = Range("C" & i & ":F" & i)
        For n = 1 To UBound(ary, 2)
            If dblDate2 < CDbl(ary(1, n)) And WorksheetFunction.IsNumber(CDbl(ary(1, n))) Then
                blnGreater = True
                 Exit For
            End If
        Next
    End If
    If blnGreater = True Then Range("A" & i).Offset(0, 1).Font.Color = vbRed
Next

End Sub

Result:
View attachment 87965

Thank you for your help, forgot to mention there are headers for each column and seems like the codes did not work when headers are present.

 

[Micron]

try changing For i = 1
to
For i = 2
if the header is just one row. If not, then adjust according to the number of used rows for header.

 

[MacroBB]

try changing For i = 1
to
For i = 2
if the header is just one row. If not, then adjust according to the number of used rows for header.

It worked with the new edit, thank you!!
Just one more question. What if the condition changed to highlighting cells in Column B if it's equal to or later/bigger than Column A but equal to or smaller than any of the following column C to F?

 

[Micron]

Try changing these parts to what I've shown

equal to or later/bigger than Column A

If dblDate1 =< dblDate2 Then

but equal to or smaller than any of the following column C to F

If dblDate2 =< CDbl(ary(1, n))...

 

[MacroBB]

Try changing these parts to what I've shown

If dblDate1 =< dblDate2 Then


If dblDate2 =< CDbl(ary(1, n))...

Thank you again! You made my day!!!

 

[Micron]

You're welcome, and thanks for the recognition.

 

[MacroBB]

@Micron
If I may...
What if the situation changed and I want to have two highlight colors? Say one for equal to and another color for smaller?