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

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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Well this will Toggle your cell color.
You did not say what cell.
So I choose Range("G3")
Code:
Sub My_Case()
'Modified  9/16/2018  2:31:23 AM  EDT
With Range("G3").Interior
    
    Select Case Range("G3").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
Well this will Toggle your cell color.
You did not say what cell.
So I choose Range("G3")
Code:
Sub My_Case()
'Modified  9/16/2018  2:31:23 AM  EDT
With Range("G3").Interior
    
    Select Case Range("G3").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
Here is another way to write your macro...
Code:
[table="width: 500"]
[tr]
	[td]Sub MyNonCase()
  Range("G3").Interior.ColorIndex = Evaluate("LOOKUP(" & Range("G3").Interior.ColorIndex & ",{-4142,6,15,17},{6,15,17,-4142})")
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Thanks But I do not understand this type script.
Do you have one to toggle the borders like the user wants. I have nothing.

Here is another way to write your macro...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MyNonCase()
  Range("G3").Interior.ColorIndex = Evaluate("LOOKUP(" & Range("G3").Interior.ColorIndex & ",{-4142,6,15,17},{6,15,17,-4142})")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Do you have one to toggle the borders like the user wants. I have nothing.
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).
 
Upvote 0
My thoughts also.
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).
 
Upvote 0
Try this:
Applies to active cell
Code:
Sub My_Case()
'Modified  9/16/2018  8:41:13 AM  EDT
With ActiveCell.Interior
    
    Select Case ActiveCell.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
Try this:
Applies to active cell
Code:
Sub My_Case()
'Modified  9/16/2018  8:41:13 AM  EDT
With ActiveCell.Interior
    
    Select Case ActiveCell.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

I works perfectly! Thanks! I assume the border toggle uses a similar structure. Where I messed up was with the usage of the "with" and "select" language.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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