Highlight cell with next date

[redspanna]

Hi all

Is there some VB code that will take a date from cell A1 and then check the dates stored through range E3:J7 and highlight the next date for each row...
so In this example below if today is 14FEB then G3 F4 F5 G6 and G7 will all be highlighted as they are next date from the date in A1, ie 14FEB

CreditCards.xlsm
	E	F	G	H	I	J
3	30 Jan	13 Feb	27 Feb	13 Mar	27 Mar	10 Apr
4	27 Jan	24 Feb	24 Mar	21 Apr	19 May	16 Jun
5	24 Jan	24 Feb	24 Mar	24 Apr	24 May	24 Jun
6	04 Jan	01 Feb	01 Mar	29 Mar	26 Apr	24 May
7	04 Jan	01 Feb	01 Mar	29 Mar	26 Apr	24 May
Sheet2

Hope that makes sense and many thanks in advance

 

[rollis13]

You could use this macro; at the moment it's static but you could use variables for dynamic ranges.

Option Explicit
Sub Highlight_Later_Dates()
    Dim rw     As Long
    Dim cl     As Long
    Range("E3:J7").Interior.Color = xlNone
    For rw = 3 To 7
        For cl = 5 To 10
            If Cells(rw, cl).Value > Cells(1, 1).Value Then
                Cells(rw, cl).Interior.Color = vbYellow
                Exit For
            End If
        Next cl
    Next rw
End Sub

 

[redspanna]

You could use this macro; at the moment it's static but you could use variables for dynamic ranges.

Option Explicit
Sub Highlight_Later_Dates()
    Dim rw     As Long
    Dim cl     As Long
    Range("E3:J7").Interior.Color = xlNone
    For rw = 3 To 7
        For cl = 5 To 10
            If Cells(rw, cl).Value > Cells(1, 1).Value Then
                Cells(rw, cl).Interior.Color = vbYellow
                Exit For
            End If
        Next cl
    Next rw
End Sub

Perfect - that will work nicely - thanks a lot

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.