Find Maximum Value Corresponding To Adjacent Column

[shonu]

Hello Everyone,

Thanks again for the above, I was able to get it to work. Now I have another sub project.

I have about 3000 rows of data which consists of phone numbers and the date they called. Column A has phone numbers and Column B has the date they called. Phone numbers are repeated in column A and I have sorted it them. I have to choose if the phone number is the same, find the recent date that they called and highlight it. Sometimes people have called multiple times on the same date. Below is my code and I am getting run time error. The code should go only to 3000 rows however it is going through the entire excel.



Sub greaterdate()

Sheets("Sheet1").Select


Dim a As Long
Dim b As Long
Dim x As Date
Dim y As Date


count = 2

a = Cells(count, 1).Value
b = Cells(count + 1, 1).Value
x = Cells(count, 2).Value
y = Cells(count + 1, 2).Value


Do While a = b


If x < y Then

Cells(count, 2).Interior.Color = RGB(255, 0, 0)

ElseIf x = y Then
Cells(count + 1, 2).Interior.Color = RGB(255, 0, 0)

ElseIf x > y Then
Cells(count + 1, 2).Interior.Color = RGB(255, 0, 0)

End If


count = count + 2

Loop



End Sub

[Admin]

Hi shonu,

Please use code tags while posting code. This time I added for you.

Once the original question is answered, start new thread for any further question.

[Excel Fox]

Select Range("B2:B3000"), and use =$B2=MAX((A2=$A$2:$A$100)*($B$2:$B$100)) as conditional format and put the required format that you need

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533)
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468 (https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468)
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411 (https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

This works just fine. Not sure why you are after VBA.

[shonu]

I don't think the Max function would work in my scenario. Let me show you a sample of the data:
Column A(Phone Numbers) Column B(the date they called)
1234567890 19/01/2011
1234567890 20/02/2009
2345678901 25/03/2013
2345678901 23/03/2013

Imagine 3000 rows of different phone numbers being repeated sometime 2 to 4 times with different dates. I have to choose and highlight the recent date. Due to the sample size, I thought a VBA code would be better.
I can use the Max If function however if the sample size was small and I am looking for a particular highest data.
Please let me know your thoughts.
Thanks

[Excel Fox]

This works just fine. Not sure why you are after VBA.

[shonu]

thank you for attaching the file. I get it now.