Excel find lastrow of same values of column and highlight it.

[ExcelN123]

Hi Genius minds,

Was hoping if someone can help me with what i am trying to achieve here thru VBA or any?.

Goal is to auto highlight the lastrow for each different Start time which for example at column "E" is "5:00 PM", "5:30 PM", "6:00 PM", "7:00 PM".

SamplePic1.JPG

[ironfelix717]

Hi,

This could be done in VBA of course. But a simple formula can solve this, then conditional formatting based on the result of the formula.

See attached book. This assumes that times are listed in chronological order and will not function properly if they are not.

Note: The conditional formatting is not highlighting the entire row, just driver name in my example. Don't have time to **** with Excels PITA conditional formatting rules.

Edit: BTW, i think your example image is incorrect towards the bottom...

[jindon]

Based on your picture
If you want highlite max row of every 30 min then

1) Select B2:G400 for example.
2) Go to Conditional formattig and formula
=AND(OR(MINUTE($E2)=0,MINUTE($E2)=30),$E2<>"",ROW($E2)=MAX(IF($E$2:$E$400=$E2,ROW($E$2:$E$400))))

[ExcelN123]

Nice guys, Thanks a lot ironfelix717 for the help.

Thanks again, i end up with this code and works just fine.

With Sheets("sheet1").Range("A9:G100")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($F9=1)"
With .FormatConditions(.FormatConditions.count)
.SetFirstPriority
Sheets("sheet1").Range("A9:G100").Select
With .Interior
.Color = 65535
End With
End With
End With

[jindon]

No extra working column...

Please Login or Register  to view this content.