1) macro to toggle cell border and 2) macro to toggle cell color fill

P

pabloevan

New Member
Joined
Sep 15, 2018
Messages
15
Hey guys,

I've been racking my brain trying come up with 2 separate macros:

macro 1) toggle cell border between borders: top, bottom, left, right, outside, top and double bottom, none
macro 2) toggle cell interior color between: yellow, light gray, light blue, no fill

I tried using the "case" function in vba but can't get it to work.

Any help is appreciated.

Thanks,
Paul
 
Sort by date Sort by votes
pabloevan said:
I'm trying to tweak that macro now to allow me to apply this same border toggle to an active cell, highlighted cluster of cells, row, or column.
Click to expand...
I wrote in Message #5 "Before writing code to to this, I think I would want to know the type of range the OP wants to apply this to first (single cell, range of cells in a single row, range of cells in single column or a rectangular range of cells)." to which you responded in Message #7 with "I need it for the active cell. Thx". If you needed it for more than the active cell, you should have said so as the code will (probably) be different (see the "Please Note" section below). In order to rewrite the code, we need to know what should happen to the cells inside a rectangular range.. do they get borders or are the borders only around the outside of the selected cells? If the inside cells get borders, how is the double bottom border applied... to every single cell or only the outside bottom edge? Be specific in your description.


A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Rick Rothstein said:
I wrote in Message #5 "Before writing code to to this, I think I would want to know the type of range the OP wants to apply this to first (single cell, range of cells in a single row, range of cells in single column or a rectangular range of cells)." to which you responded in Message #7 with "I need it for the active cell. Thx". If you needed it for more than the active cell, you should have said so as the code will (probably) be different (see the "Please Note" section below). In order to rewrite the code, we need to know what should happen to the cells inside a rectangular range.. do they get borders or are the borders only around the outside of the selected cells? If the inside cells get borders, how is the double bottom border applied... to every single cell or only the outside bottom edge? Be specific in your description.


A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
Click to expand...

Fair enough and I apologize. Newbie mistake. I assumed it would be easy to be lead in the right direction with "active cell" and then I would be able to manipulate it on my own once I got that foundation. I guess going straight to the point is the most effective way for everybody.

I've worked in excel everyday for my job for the past 17 years. Speed in execution in completing our financial analysis is crucial and I figured some macros to save time would help out the team. When we do financial modelling, the functions that take the longest to key in without using the mouse are: font color, cell fill color, alignment, number format, and borders. I was able to create macros to toggle all of them except cell fill color and borders. With the forum's help, I got the "cell fill color" toggle to work for my needs. A border toggle is all I need left for this stage.

Yesterday was the first time I have ever tried to manipulate/write macros and I'm learning a lot through research and my own trial and error. This has been a lot of fun. Hopefully one day I can pay it forward and start to help others.

To clarify each border scenario for one active cell, selected block of cells, row, or column:
1. top: border only applies to the cells at the top of the selected area (no border elsewhere)
2. left:
border only applies to the cells at the left of the selected area
(no border elsewhere)​
3. right
:
border ​
only applies to the cells at the right of the selected area
(no border elsewhere)​
4. bottom
:
border ​
only applies to the cells at the bottom of the selected area
(no border elsewhere)​
5. outside:
only draws outside border around the selected area
(no border elsewhere)​
6. top and double bottom: single line at the top of the selected area, double line at the bottom of the selected area
(no border elsewhere)​
7. none: applies to all cells in the selected area

Please let me know if this clarifies.

Thanks​
​
​
​
<strike>
</strike>​
 
Upvote 0
sorry for the weird formatting in my last post. it looked fine when I typed it in but when I posted it, it got all messed up
 
Upvote 0
I think the following macro will do what you want...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ToggleBorders()
  Dim CurBorder As Long, Brdr As String, B As Variant
  With Selection
    If Not .Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeTop
    If Not .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeLeft
    If Not .Borders(xlEdgeRight).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeRight
    If Not .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeBottom
    Brdr = Trim(Brdr)
    If Len(Brdr) = 0 Then
      .Borders(xlEdgeTop).LineStyle = xlContinuous
    ElseIf InStr(Brdr, " ") = 0 Then
      Select Case Brdr
        Case xlEdgeTop
          .Rows(1).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeLeft).LineStyle = xlContinuous
        Case xlEdgeLeft
          .Columns(1).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeRight).LineStyle = xlContinuous
        Case xlEdgeRight
          .Columns(.Columns.Count).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeBottom).LineStyle = xlContinuous
        Case xlEdgeBottom
          .Rows(.Rows.Count).Borders(Brdr).LineStyle = xlLineStyleNone
          .BorderAround xlContinuous
      End Select
    ElseIf Brdr Like "* * * *" Then
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
      .Borders(xlEdgeRight).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlDouble
    Else
      .Borders(xlEdgeTop).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
    End If
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
I think the following macro will do what you want...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ToggleBorders()
  Dim CurBorder As Long, Brdr As String, B As Variant
  With Selection
    If Not .Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeTop
    If Not .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeLeft
    If Not .Borders(xlEdgeRight).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeRight
    If Not .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone Then Brdr = Brdr & " " & xlEdgeBottom
    Brdr = Trim(Brdr)
    If Len(Brdr) = 0 Then
      .Borders(xlEdgeTop).LineStyle = xlContinuous
    ElseIf InStr(Brdr, " ") = 0 Then
      Select Case Brdr
        Case xlEdgeTop
          .Rows(1).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeLeft).LineStyle = xlContinuous
        Case xlEdgeLeft
          .Columns(1).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeRight).LineStyle = xlContinuous
        Case xlEdgeRight
          .Columns(.Columns.Count).Borders(Brdr).LineStyle = xlLineStyleNone
          .Borders(xlEdgeBottom).LineStyle = xlContinuous
        Case xlEdgeBottom
          .Rows(.Rows.Count).Borders(Brdr).LineStyle = xlLineStyleNone
          .BorderAround xlContinuous
      End Select
    ElseIf Brdr Like "* * * *" Then
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
      .Borders(xlEdgeRight).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlDouble
    Else
      .Borders(xlEdgeTop).LineStyle = xlLineStyleNone
      .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
    End If
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Works like a charm! I'm learning on a real time basis the nuances of how you need to write the code for active cell, designated range, and other areas. This has been very educational for me. Getting the border to toggle was definitely more complicated than the other functions I worked on like number format, cell font color, and alignment. Thanks a bunch
 
Upvote 0
pabloevan said:
Thanks for the reply. On the color one, one of the other posters suggested something that worked so I figured no need to recreate the wheel. I'll take note of the technique you used to toggle the colors for future needs.

here is what I did on the toggle color. This allowed me to highlight multiple cells, row, column or a single cell and toggle the cell color

Sub cellfill()
'
' cellfill Macro
'
' Keyboard Shortcut: Ctrl+s
'
With ActiveCell.Interior
Select Case ActiveCell.Interior.ColorIndex
Case 6 'Yellow
Selection.Interior.ColorIndex = 15 'Light Gray
Case 15 'Light Gray
Selection.Interior.ColorIndex = 34 'Light Blue
Case 34 'Light Blue
Selection.Interior.ColorIndex = -4142 'No color
Case -4142 'No color
Selection.Interior.ColorIndex = 6 'Yellow
End Select
End With
End Sub
Click to expand...


Glad to see you now have what you want.
You originally did not say what cell or cells then after providing my script you said active cell.
But now I see you wanted selected cells.
It's always best to provide those specific details at the beginning of your post.
But glad to see you know how to modify scripts to your needs. Take care.
 
Upvote 0
You could have modified my script to look like this.
eliminates needing selection in front of every line of code.
Code: 
Sub My_New_One()
'Modified  9/16/2018  4:34:13 AM  EDT
With Selection.Interior
    
    Select Case Selection.Interior.ColorIndex
    Case 6 'Yellow
        .ColorIndex = 15 'Light Gray
    Case 15 'Light Gray
        .ColorIndex = 17 'Light Blue
    Case 17 'Light Blue
        .ColorIndex = -4142 'No color
    Case -4142 'No color
        .ColorIndex = 6 'Yellow
    End Select
End With
End Sub
 
Upvote 0
My Aswer Is This said:
You could have modified my script to look like this.
eliminates needing selection in front of every line of code.
Code: 
Sub My_New_One()
'Modified  9/16/2018  4:34:13 AM  EDT
With Selection.Interior
    
    Select Case Selection.Interior.ColorIndex
    Case 6 'Yellow
        .ColorIndex = 15 'Light Gray
    Case 15 'Light Gray
        .ColorIndex = 17 'Light Blue
    Case 17 'Light Blue
        .ColorIndex = -4142 'No color
    Case -4142 'No color
        .ColorIndex = 6 'Yellow
    End Select
End With
End Sub
Click to expand...

Very nice! thanks for the tip. I'm all for making the coding as efficient as possible
 
Upvote 0
Here is another example of a script like you asked about.

This script will also enter the color name in the cell.
Just so you can see other ways to use case:
This is a vague name not exact like Light Gray Or Light Blue
Code: 
Sub New_One_With_Text()
'Modified  9/18/2018  11:05:13 PM  EDT
With Selection
    Select Case Selection.Interior.ColorIndex
    Case 6 'Yellow
        .Interior.ColorIndex = 15 'Light Gray
        .Value = "Light Gray"
    Case 15 'Light Gray
        .Interior.ColorIndex = 17 'Light Blue
        .Value = "Light Blue"
    Case 17 'Light Blue
        .Interior.ColorIndex = -4142 'No color
        .Value = "No Color"
    Case -4142 'No color
        .Interior.ColorIndex = 6 'Yellow
        .Value = "Yellow"
    End Select
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Help writing macros to toggle through various formatting (font color, cell fill color, number format, decimals, borders)
Replies
1
Views
801
jolivanes
jolivanes
L
  • Question Question
VBA to change cell color based on existing cell color
Replies
9
Views
1K
Joe4
Joe4
C
  • Solved
VBA Fill rows with color, but skip cells with already certain color fills
Replies
5
Views
3K
Zot
Z
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
M
  • Solved
Fill cell color without deleting conditional formatting
Replies
1
Views
910
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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