Macro for underline

Dan_var

Board Regular
Joined
Jan 22, 2009
Messages
92
Hello,

Cells A1 to A5 have :
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" width=64 height=18>1 xyz</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>2 xyz</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>3 xyz</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>4 abc</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>5 abc</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>6 def</TD></TR></TBODY></TABLE>
I would like to underline rows 3, 5 and 6 (that is before the value in A changes to the next value).

Also if possible, to clear the underlines in prior rows - that is if row 2 has an underline that should be cleared and only row 3 to be undelined.

Can anybody help with a simple macro ? Appreciate your time...

thanks...Dan
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Re: Macro for undeline

Hello,

Cells A1 to A5 have :
<table style="width: 48pt; border-collapse: collapse;" x:str="" width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); width: 48pt; height: 13.2pt; background-color: transparent;" width="64" height="18">1 xyz</td></tr><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); height: 13.2pt; background-color: transparent;" height="18">2 xyz</td></tr><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); height: 13.2pt; background-color: transparent;" height="18">3 xyz</td></tr><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); height: 13.2pt; background-color: transparent;" height="18">4 abc</td></tr><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); height: 13.2pt; background-color: transparent;" height="18">5 abc</td></tr><tr style="height: 13.2pt;" height="18"><td style="border: medium none rgb(224, 223, 227); height: 13.2pt; background-color: transparent;" height="18">6 def</td></tr></tbody></table>
I would like to underline rows 3, 5 and 6 (that is before the value in A changes to the next value).

Also if possible, to clear the underlines in prior rows - that is if row 2 has an underline that should be cleared and only row 3 to be undelined.

Can anybody help with a simple macro ? Appreciate your time...

thanks...Dan
If I understand what you want correctly, this will work:
Code:
Sub AddUnderline()
Dim rng As Range, lRw As Long

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2", "A" & lRw)
With Range("A1", "A" & lRw).Font
    .Underline = False
End With
For Each c In rng
    If c.Value = c.Offset(-1, 0).Value And c.Value <> c.Offset(1, 0).Value Or c.Row = lRw Then
        With c.Font
            .Underline = True
        End With
    End If
Next c
    
End Sub
 
Upvote 0
Re: Macro for undeline

Hello JoeMo,

thank you for your prompt reply. Can you change the code to underline the entire row and not just the Cells ? By underline I mean the one in the "Borders" and not the one in "U".

Thank you and sorry for the confusion.

dan
 
Upvote 0
Re: Macro for undeline

Hello JoeMo,

thank you for your prompt reply. Can you change the code to underline the entire row and not just the Cells ? By underline I mean the one in the "Borders" and not the one in "U".

Thank you and sorry for the confusion.

dan
Do you want to put a bottom border (line) across the entire row or just across the part of the row that falls within the used range? For example, if the used part of a row extends from column A through Column M, do you want the border to terminate at column M? If you want to run the border across an entire row, be aware that if you have many such rows, your file may become rather large (in terms of file size) and slow to load, especially if you are using Excel 2007 or Excel 2010 where there are more than 16,000 columns.
 
Upvote 0
Re: Macro for undeline

Hello,

Yes I need the border line to run from Cols A to AL only. Thanks for pointing out...dan
 
Upvote 0
Re: Macro for undeline

Hello,

Yes I need the border line to run from Cols A to AL only. Thanks for pointing out...dan
OK -try this:
Code:
Sub AddBottomBorderToSpecifiedRange()
Dim rng As Range, lRw As Long, lCol As Long

lRw = Range("A" & Rows.Count).End(xlUp).Row
lCol = Columns("AL:AL").Column
Set rng = Range(Cells(1, 1), Cells(lRw, lCol))
With rng.Resize(lRw + 1)
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
For Each c In rng.Columns(1).Cells
    If c.Value <> c.Offset(1, 0).Value Then
        With c.Resize(1, lCol).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlMedium
        End With
    End If
Next c
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top