Help - Apply Formatting To Current Row

[Heiko]

Hello All,

I recorded a formula to apply a thick green border on the bottom of
the current row.

The recorded macro stored the following:

Sub FatGreen()
'
' FatGreen Macro
' Macro recorded 8/4/2007 by kili
'
Rows("104:104").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 4
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
' Range("A104").Select
End Sub

I'd like to be able to run this macro and have it apply on any current
/ selected row, but obviously the hardcoded Row number is the problem.

I've spent the last few hours on google's newsgroups archive's and
have come up empty-handed.

Any VBA suggestions would be much appreciated.

Thank you,
Heiko

 

[Guest]

To keep from having to change it all too much, try this
Sub FatGreen()
'
' FatGreen Macro
' Macro recorded 8/4/2007 by kili
'
Dim theRow As Range
Set theRow = Selection.EntireRow

theRow.Borders(xlDiagonalDown).LineStyle = xlNone
theRow.Borders(xlDiagonalUp).LineStyle = xlNone
theRow.Borders(xlEdgeLeft).LineStyle = xlNone
With theRow.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 4
End With
theRow.Borders(xlEdgeRight).LineStyle = xlNone
theRow.Borders(xlInsideVertical).LineStyle = xlNone

End Sub

 

[Dallman Ross]

I recorded a formula to apply a thick green border on the bottom of
the current row.

[. . . .]

I'd like to be able to run this macro and have it apply on any
current / selected row, but obviously the hardcoded Row number is
the problem.

Sub Macro1()
With Selection
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 4
End With

.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub

 

[Guest]

Dallman,
I know that the OP said "any selected row", and indeed, if he has entire row
selected when he runs your macro, it works as he desires. As such, it fits
the bill exactly!

To explain why I did it the way I did: I kind of presumed that often people
don't say what they mean here in the forums, and that they might have just a
single cell selected in a row - in which case, the code I tossed together
would make up for a little laxness on the part of the end user.

Heiku: now you have 2 versions to choose from and an explanation of the
differences between the two. Dallman's is superior to mine in that if you
know you'll always select the entire row, it does not have to use the
overhead mine does to achieve the same result.

I recorded a formula to apply a thick green border on the bottom of
the current row.

[. . . .]

I'd like to be able to run this macro and have it apply on any
current / selected row, but obviously the hardcoded Row number is
the problem.

Sub Macro1()
With Selection
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 4
End With

.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub

---
dman

==============================================================

The recorded macro stored the following:

Sub FatGreen()
'
' FatGreen Macro
' Macro recorded 8/4/2007 by kili
'
Rows("104:104").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 4
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
' Range("A104").Select
End Sub

I'd like to be able to run this macro and have it apply on any current
/ selected row, but obviously the hardcoded Row number is the problem.

I've spent the last few hours on google's newsgroups archive's and
have come up empty-handed.

Any VBA suggestions would be much appreciated.

Thank you,
Heiko

 

[Heiko]

I know that the OP said "any selected row", and indeed, if he has entire row
selected when he runs your macro, it works as he desires. As such, it fits
the bill exactly!

To explain why I did it the way I did: I kind of presumed that often people
don't say what they mean here in the forums, and that they might have just a
single cell selected in a row - in which case, the code I tossed together
would make up for a little laxness on the part of the end user.

Heiku: now you have 2 versions to choose from and an explanation of the
differences between the two. Dallman's is superior to mine in that if you
know you'll always select the entire row, it does not have to use the
overhead mine does to achieve the same result.

Hello,

You were correct; I usually do not have the *entire row* selected when
I would like this macro to run.

Your script runs perfectly, and I appreciate your help.

Best regards,

Heiko

 

[Dallman Ross]

Dallman,
I know that the OP said "any selected row", and indeed, if he
has entire row selected when he runs your macro, it works as he
desires. As such, it fits the bill exactly!

Glad Heiko's problem is solved.

I'm also glad you followed up like this, because I didn't see any
other followup to him other than mine. I don't see your code! My
newsserver has holes in these articles.

I now suspect others have been answering me in some of the threads I've
started or contributed to, but I cannot see many of those answers.

I asked my news administrator about this the other day, and got this
answer about one followup I only saw quoted in another poster's answer:

The post this was following up to (with the Message-ID of
<[email protected]>) was
rejected by Cleanfeed. The particular test that it triggered
is one that takes a hash of article headers (in this case,
Posting-Host and number of lines) and, if too many come in
with the same hash in the same period of time, rejects them as
likely spam.

The admin goes on to say this may well be happening to other articles
here. I'm sure it is! :-(

To explain why I did it the way I did: I kind of presumed that
often people don't say what they mean here in the forums, and
that they might have just a single cell selected in a row - in
which case, the code I tossed together would make up for a little
laxness on the part of the end user.

On the other hand, the line is only at the bottom of the range. So
if he selects just the bottom cell in the row, my version will also
work. But he seems, in his followup, to be enamored of yours, so --
congrats. Maybe I'll find your code on the web or something.
I suspect I know what it says, more or less, based on your comments,
however.

Dallman

 

[Guest]

I've heard of others having troubles lately in seeing posts in these forums -
particularly when attending through Google groups. The Google folks are
supposed to be working on their end of it. I don't know if that's got
anything to do with the problem you're experiencing or not. But it does
answer the one big question that so many have asked for so long: "Am I alone
in all of this?" And the answer is a resounding "NO! You are not alone." <g>

Things are always dynamic around here: one day viewing with web browser
works fine, next day it doesn't and a newsreader is needed, then the whole
thing flip flops a week later, then during the 4th quarter of the moon after
the 2nd full moon following the vernal equinox (barring sufficient
sacrificial offerings to appease the newsreader gods) NOTHING works! <G>

Heiku, glad that we could assist you. Enjoy.