Underlining

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a worksheet that has approximately 60,000 line
entries with 6 columns of information on each line. The
first column is a list of item numbers that can have
anywhere from 2 to 36 subsequent rows beneath it with the
same item number. I would like to underline the entire
row where the item number changes. How can I do
this "automatically" without visually looking through the
entire worksheet?
 
Select the entire range of data, do Format / Conditional Formatting, change 'Cell value is' to
'Formula is', click on cell (Assuming data excluding headers starts in A2) A2, and hit F4 twice so
that it changes from =$A$2 to =$A2, then type <> and then click on cell A3 and hit F4 twice so
that it changes from =$A2<>$A$3 to =$A2<>$A3 ( Or you could just type in =$A2<>$A3 to start with
:-> ), click on the Format button and select the 'Border' tab and choose a bottom border. then
just hit OK till you are out. This will be dynamic, so that as you change any data in Col A, the
row underlining will change to reflect the new data.
 
One way to add the borders:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
FirstRow = 3 '2 header rows for me
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'clean up existing borders
With .Cells(FirstRow, "A").Resize(LastRow - FirstRow + 1, 6)
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
End With

For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'don't underline
Else
With .Cells(iRow, "A").Resize(1, 6) '6 columns
With .Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 3
End With
End With
End If
Next iRow
End With

Application.ScreenUpdating = True

End Sub

Record a macro when you do it once so you'll get your choice of colors and style
in the borders.
 
Back
Top