Selecting newest date from a column

lgroulx2000

Board Regular
Joined
Jan 6, 2009
Messages
100
Hi,

I want to seach and select the newest date in a column. There are green, red and black fonts in the column. I only want to only seach the cells with black fonts.

Thanks,

Larry
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Okay, I presume you are looking for vba code.

How are the font colors chosen? That is, were certain dates actually typed in different colors, or, is the font color a result of CF?

Mark
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
The newest (most recently entered) date?....or the newest (most recent in time) date?

Code:
=max(A:A)

will find the most recent (in time) date in column A

EDIT: HOLY:eek::eek:

Completely missed the part about black font only.
 

lgroulx2000

Board Regular
Joined
Jan 6, 2009
Messages
100
Hi,

There are 3 radio buttons on a user form. Selecting a button changes the font colour on the date inserted in the cell.

Thanks,

Larry
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147

ADVERTISEMENT

Modified from here:

http://www.mrexcel.com/forum/showpost.php?p=2159648&postcount=6

Put this UDF in a module

Code:
Function ColourMax(MyRange As Range, ColourCode As String)
Dim ColourCodeValue As Long
If IsNumeric(ColourCode) Then
    ColourCodeValue = Val(ColourCode)
Else
    ColourCodeValue = Range(ColourCode).Interior.Color
End If
For Each cell In MyRange
    If cell.Interior.Color = ColourCodeValue And cell.Value > ColourMax Then
        ColourMax = cell.Value
    End If
Next
End Function

Use by either:

Highlight a shaded cell and run this code.
Code:
Sub FindColour()
InputBox "This is your colour code", "ColourCode", ActiveCell.Interior.Color
End Sub

Copy the number that pops up and insert in to the below fomula

=ColourMax(D5:D7,5296274) where D5:D7 is the range

OR

=ColourMax(D5:D7,"A2")

Where D5:D7 is the range and A2 is a shaded cell with the colour you want to use in your match.

Note you MUST put double quotes around the A2

The result will come back as a value. This can be used on numbers OR dates. Just format the result as a date in the cell format section.

Cheers

Dan
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Sorry, that was for cell colour. This is font colour:

Code:
Sub FindColour()
InputBox "This is your colour code", "ColourCode", ActiveCell.Font.Color
End Sub
 
Function ColourMax(MyRange As Range, ColourCode As String)
Dim ColourCodeValue As Long
If IsNumeric(ColourCode) Then
    ColourCodeValue = Val(ColourCode)
Else
    ColourCodeValue = Range(ColourCode).Font.Color
End If
For Each cell In MyRange
    If cell.Font.Color = ColourCodeValue And cell.Value > ColourMax Then
        ColourMax = cell.Value
    End If
Next
End Function

Same instructions as above though.
 

lgroulx2000

Board Regular
Joined
Jan 6, 2009
Messages
100

ADVERTISEMENT

Thanks Dan,

I tried your function. The colour index for font xlAutomatic came back as 16777215.

The function still selected the cell with the green font as the max value.

Larry
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top