Apply bottom border only on filled cells, leaves blank cells without border?
 

I need to apply a bottom border to only filled cells. Archives didn't
yield anything pertinent that I could find but I was able to figure
which line style by recording the keystrokes. I need the hairline
style on the bottom edge, if this is any help:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

Thanks so much. :oD

Apply bottom border only on filled cells, leaves blank cells without border?
 

Hi StargateFan,

try this:
Sub Test4000()
Dim oCll As Range
For Each oCll In ActiveSheet.UsedRange
If oCll.Value < "" Then
oCll.Borders(xlEdgeBottom).LineStyle = xlContinuous
oCll.Borders(xlEdgeBottom).Weight = xlMedium
oCll.Borders(xlEdgeBottom).ColorIndex = 3
End If
Next
End Sub


--
Greetings from Bavaria, Germany

Helmut Weber
Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Apply bottom border only on filled cells, leaves blank cells without border?
 

On Sat, 07 Apr 2007 22:45:28 -0400, StargateFan
wrote:

I need to apply a bottom border to only filled cells. Archives didn't
yield anything pertinent that I could find but I was able to figure
which line style by recording the keystrokes. I need the hairline
style on the bottom edge, if this is any help:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

Thanks so much. :oD

On Sun, 08 Apr 2007 10:44:18 +0200, Helmut Weber
wrote:

Hi StargateFan,

try this:
Sub Test4000()
Dim oCll As Range
For Each oCll In ActiveSheet.UsedRange
If oCll.Value < "" Then
oCll.Borders(xlEdgeBottom).LineStyle = xlContinuous
oCll.Borders(xlEdgeBottom).Weight = xlMedium
oCll.Borders(xlEdgeBottom).ColorIndex = 3
End If
Next
End Sub


--
Greetings from Bavaria, Germany

Greetings, Bavaria! <g

This is cool. It almost works. I realized that a colour should be
defined as well as adding the above hairline code in for the weight so
it now reads like this:

Sub zPutBottomBorderOnSelectedCellsThatHaveTEXT()
Dim oCll As Range
For Each oCll In ActiveSheet.UsedRange
If oCll.Value < "" Then
oCll.Borders(xlEdgeBottom).LineStyle = xlContinuous
oCll.Borders(xlEdgeBottom).Weight = xlHairline
oCll.Borders(xlEdgeBottom).ColorIndex = 1
End If
Next
End Sub

However, a couple of things hopefully can be finetuned. Instead of
putting a border just on the cells that have text in the selected
area, it puts a border on everything on the rows of the selected cells
which defeats the purpose <g.

Also, there are very small columns separating the larger columns that
can contain text. This is in an effort to have a gap between the
individual letters so that wherever a letter should go, it's
represented by this empty long dash for each done by the bottom
border. Once the borders are put in, I will just delete the letters
themselves. This is so that the user knows how many letters are
supposed to be in the answer. Since the above seems to be putting a
border under even these separator cells with no text, the result is
one long line instead of "dashes".

Can this be fixed so that the macro _only_ underlines non-empty cells
in a selection of cells rather than all the rows in the selection?

Thanks. It's a great beginning. :oD

Apply bottom border only on filled cells, leaves blank cellswithout border?
 

You could also use conditional formatting to apply the border:
Select the cells, and choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell (you
can see its address in the Name box, to the left of the Formula Bar):
=A1<""
Click Format, and on the Borders tab select a colour for the border, and
click the bottom border in the diagram.

StargateFan wrote:
I need to apply a bottom border to only filled cells. Archives didn't
yield anything pertinent that I could find but I was able to figure
which line style by recording the keystrokes. I need the hairline
style on the bottom edge, if this is any help:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

Thanks so much. :oD



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Apply bottom border only on filled cells, leaves blank cells without border?
 

Hi StargateFan,

are you speaking of text as opposed to digits
and just characters other than a-z and A-Z?

Then, if UCase(cell.value) < LCase(cell.value)
there is at least one letter in it.

Instead of putting a border just on the cells
that have text in the selected
area, it puts a border on everything
on the rows of the selected cells
which defeats the purpose <g.

Hmm...

See also: http://tinyurl.com/2jbleh
for empty vs null vs ""

Sub Test4000B()
Dim oCll As Range
For Each oCll In ActiveSheet.UsedRange
If Not LCase(oCll.Value) = UCase(oCll.Value) Then
oCll.Borders(xlEdgeBottom).LineStyle = xlContinuous
oCll.Borders(xlEdgeBottom).Weight = xlHairline
oCll.Borders(xlEdgeBottom).ColorIndex = 1
Else
' otherwise remove border, skip this one if you like
oCll.Borders(xlEdgeBottom).LineStyle = xlNone
End If
Next
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"