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

On Sat, 07 Apr 2007 22:45:28 -0400, StargateFan
<IDon'tAcceptSpam@IDon'tAcceptSpam.com> 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
<nbhymsjxdgcn@xxxxxxxxxxxxxx> wrote:

Hi StargateFan,

try this:
Sub Test4000()
Dim oCll As Range
For Each oCll In Active***.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 Active***.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

.