How do to underline rows in excel based on unique data?

I'm trying to automatically underline certain rows based on unique project IDs in column 1. Please see example table attached. Each Project ID may have different number of rows associated each time and i want to separate every project with a bottom line but not every row. I have been trying to figure out the correct formula to enter under conditional formatting. Many thanks

Example table:

2 Answers

The conditional formatting rule would be:

=AND($A2<>"",$A2<>$A1)

With an "Applies To" range of $A$2:$D$17

Set the format to have a top border.

I would like to suggest you the fastest method, is use of a MACRO.

Check the Screen Shot:

 Sub AddLineWhenValueChanges() Application.ScreenUpdating = False Dim LastRow As Long Dim xrg As Range LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For Each xrg In Range("A2:A" & LastRow) If xrg <> xrg.Offset(1, 0) Then Range("A" & xrg.Row & ":D" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous End If Next xrg Application.ScreenUpdating = True
End Sub

N.B. Insert this VBA code as Module with the Sheet.