Selecting newest date from a column

[lgroulx2000]

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

 

[GTO]

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]

The newest (most recently entered) date?....or the newest (most recent in time) date?

=max(A:A)

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

EDIT: HOLY

Completely missed the part about black font only.

 

[lgroulx2000]

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]

Modified from here:

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

Put this UDF in a module

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.

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]

Sorry, that was for cell colour. This is font colour:

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]

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

 

[Blade Hunter]

Automatic on mine is 0

 

[lgroulx2000]

Hi,

Typed in 0 and works like a charm.

Thanks,

Larry

 

[Blade Hunter]

Any time, just lucky there was such a similar question 5 mins before yours