2

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:

3
  • What example data? There is nothing attached.
    – DavidPostill
    May 23, 2018 at 14:14
  • 2
    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.
    – DavidPostill
    May 23, 2018 at 14:14
  • Is the example table completed like you want it? Do you not want to separate IDs past row 8?
    – BruceWayne
    May 24, 2018 at 6:09

2 Answers 2

3

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.

enter image description here

1
  • @David - Did that do the trick? If that answered your question, you can mark the post as The Answer by clicking the check mark left of the post (below the down arrow). It will also remove your question from the "unanswered" queue.
    – BruceWayne
    May 23, 2018 at 15:34
-3

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

Check the Screen Shot:

enter image description here

    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.

7
  • I'm wondering, why this has been down-voted ? Better I must raise flag to Moderator. May 23, 2018 at 16:58
  • 1
    Didn't downvote, but perhaps because OP tagged with "Conditional Formatting", and this is almost trivial to do with CF, and you posted a macro answer?
    – BruceWayne
    May 23, 2018 at 17:02
  • @BruceWayne, look the OP's "underline rows in excel based on unique data?" and in fact his IDs are group of DUPLICATEs, the Screen shot shows 1st UNDERLINE is after group of 106834 and so ahead. The Solution what you have posted is doing same, 1st UNDERLINE is after group of A's then B's NO UNIQUE !! I've suggested MACRO coz the TAG "Worksheet Function", since the VBA composes the Function. And you can easily find the MACRO is exactly doing the same what your solution did ! The OP should as UNDERLINE WHEN VALUE CHANGES instead! May 24, 2018 at 5:29
  • 2
    I didn't downvote either, but what BruceWayne said. You posted two comments worth of reasons and justification, but the bottom line is that VBA isn't a good answer, even if you had explained the code and how to adapt it, for a question that requests conditional formatting, can be done trivially with conditional formatting, and was asked by a novice who is having trouble with conditional formatting.
    – fixer1234
    May 27, 2018 at 10:18
  • 1
    You keep threatening to flag a moderator every time a bad answer gets downvoted without explanation. I haven't seen any downvotes that were not readily evident as to why, but you're encouraged to flag a moderator for any voting you think is unfair, or ask about it on meta.superuser.com. But just do it; posting a comment threatening to do it is an improper use of a comment, and counter-productive as far as affecting voting. It may attract additional downvotes as "confirmation of legitimacy" from people seeing that response to what others can recognize as a bad answer.
    – fixer1234
    May 27, 2018 at 10:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .