Conditional formatting

[mega128]

Hi everyone,

Here is an example of what I have:


Name Date Size Location
abc 7/21/03 11:26 AM 16792 c:\test
abc 12/27/2009 10:16:55 AM 221288 c:\test\temp
abc 12/28/09 10:46 AM 16732 c:\one
xyz 12/28/2003 10:16:55 AM 221288 c:\test\temp
xyz 12/28/05 10:46 AM 16732 c:\one\test
xyz 12/28/2001 10:16:55 AM 221288 c:\test\temp\a
xyz 12/28/05 10:46 AM 16732 c:\one\wy

Column "Name" consists of duplicate file names with their date, size & locations.
I would like to highlight whole row for most recent file based on the date. In above case
for abc it would highlight 12/28/09 10:46 AM and 12/28/2001 10:16:55 AM for xyz.
How can this be done in Conditional formatting?

Thanks all

[Cutter]

Set the Conditional Format to highlight the cells if the date cell for that row is equal to the max value found in the date column.

[mega128]

Set the Conditional Format to highlight the cells if the date cell for that row is equal to the max value found in the date column.

I am really sorry I am pretty new to excel conditional formatting.

I selected all Date Columns
then clicked on Conditional Formatting > Highlight Cells Rules > Greater Than
In "Format cell that are greater than" what cell should I select?

Can you please explain in little more detail?
Thank you

[Cutter]

Here you go - change the range to match your data range in the "Applies to" section of the CF window.

[mega128]

Thanks Cutter, but it highlights only one cell in B column. My problem is how to highlight MAX for each duplicate name in Column A.

I have attached a test spreadsheet.

For example:
I would like the formula to highlight December 11, 20011 for "a" and April 13, 2010 for Name "b".

Thanks again for your help.

[Cutter]

Sorry, I missed that part of your original request.

Will column B contain dates as you show - sorted ascending for A and descending for B or will it be a jumble of dates with no sorting at all? It would be simpler for a CF formula if you sorted first by col A then by col B. Is that workable?

[mega128]

Sorry, I missed that part of your original request.

Will column B contain dates as you show - sorted ascending for A and descending for B or will it be a jumble of dates with no sorting at all? It would be simpler for a CF formula if you sorted first by col A then by col B. Is that workable?

It is a jumble of dates. I will need to apply one more rule after that for the size of file.

[Cutter]

OK, try this. I used a helper column (col I) which contains array formulas.

The conditional formatting is applied to range A2:G8. Don't use whole row or column references in CF as it will bloat your file.

[mega128]

Works perfect! Thank you very much.