Use VBA to change cell formatting based on existing formatting

[Dossertoir]

Hi

I've been recording some basic formatting macros to speed up my work. I am wanting some to cycle to different formats depending on the existing format of selected cells. For example, I have the following code for including a top border on the selection:

Private Sub Top_Border()'
' Top_Border Macro
'
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With


End Sub

And the following code for including a top and a bottom border on a cell:

Private Sub Double_Border()
'
' Double_Border Macro
'
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = 0
.TintAndShade = 0
.Weight = xlThin
End With


End Sub


But what I am wanting is for when I run the macro, if there is no top border, then include the top border, if there is already a top border, then include both a top and a bottom border, and if there is already a top and bottom border, then remove both of the borders. I am just looking to utilise similar functions whilst using minimal keyboard short-cuts.

I know this is probably basic and that the above code is possibly very clunky - but I am a VBA novice!

Thanks!

 

[yky]

With Selection 'need to select your own

If .Borders(xlEdgeTop).LineStyle <> xlLineStyleNone And .Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
'delete top and bottom borders
.Borders(xlEdgeTop).LineStyle = xlLineStyleNone
.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone

ElseIf .Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
'add bottom border if top border exists
.Borders(xlEdgeBottom).LineStyle = xlContinuous

ElseIf .Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then
'add top border if it doesn't exist
.Borders(xlEdgeTop).LineStyle = xlContinuous

End If

End With

For more info, check out the following page:

https://technet.microsoft.com/en-us/library/ee692886.aspx

 

[My Aswer Is This]

Here I will give you a couple If Statements. And you try and see if you can write the script yourself.
If your not able to complete this task check back in here for more help.

Sub If_Selection()

If Selection.Borders(xlEdgeTop).LineStyle = xlContinuous Then
MsgBox "Yes Top"
'Your other code here
End If

If Selection.Borders(xlEdgeTop).LineStyle = xlContinuous And Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous Then
MsgBox "Yes Both Top and Bottom"
'Your other code here
End If
End Sub

Instead of giving you a fish. I would like to help you learn to fish.

 

[Dossertoir]

Thanks, this is exactly what I needed to get me started!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks, this is exactly what I needed to get me started!