VBA color code on oldest date

[aayaanmayank]

Hi Can someone please help me, i need a vba code where it should green color on oldest date with in one ID.

below is the sample of data.


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]ID[/TD]
[TD="class: xl65, width: 64"] Date[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]338583883[/TD]
[TD="class: xl67, align: right"] 08/11/2005[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]338583883[/TD]
[TD="class: xl67, align: right"]06/23/2003[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]352556553[/TD]
[TD="class: xl67, align: right"]06/16/2008[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]352556553[/TD]
[TD="class: xl67, align: right"]09/16/2008[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]855585635[/TD]
[TD="class: xl67, align: right"]07/22/2015[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]855585635[/TD]
[TD="class: xl67, align: right"]09/30/2017[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]855585635[/TD]
[TD="class: xl67, align: right"]09/30/2017[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]853523322[/TD]
[TD="class: xl67, align: right"]09/27/2016[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]853523322[/TD]
[TD="class: xl67, align: right"]06/21/2012[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]828533758[/TD]
[TD="class: xl67, align: right"]03/24/2009[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]828533758[/TD]
[TD="class: xl67, align: right"]09/11/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

Sub ConditionalFormatting()
Dim IdRange As Range
Dim DateRange As Range
'Adapt to your data ranges
Set IdRange = Range("A2:A12")
Set DateRange = Range("B2:B12")
For Each cell In IdRange
    If Cells(cell.Row, DateRange.Column).Value = Application.WorksheetFunction.MinIfs(DateRange, IdRange, cell.Value) Then
        Cells(cell.Row, DateRange.Column).Interior.Color = 5287936
    End If
Next cell
End Sub

 

[aayaanmayank]

thanks, but i am getting run time error 438

 

[Kamolga]

on which line?

I just checked minifs:[FONT=&quot] This feature is not available in Excel 2016 unless you have an [/FONT]Office 365 subscription[FONT=&quot]. If you are an Office 365 subscriber, [/FONT]make sure you have the latest version of Office[FONT=&quot].[/FONT]

 

[aayaanmayank]

i have office 2016

 

[MickG]

Another option:-

[COLOR="Navy"]Sub[/COLOR] MG19Nov19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) < .Item(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 1)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) And Dn.Offset(, 1) = .Item(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Resize(, 2).Interior.Color = vbGreen
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[aayaanmayank]

Its working thank you so much

 

[MickG]

You're welcome