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

[pabloevan]

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

 

[My Aswer Is This]

Well this will Toggle your cell color.
You did not say what cell.
So I choose Range("G3")

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

 

[Rick Rothstein]

Well this will Toggle your cell color.
You did not say what cell.
So I choose Range("G3")

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...

[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]

 

[My Aswer Is This]

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...

[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]

 

[Rick Rothstein]

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).

 

[My Aswer Is This]

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).

 

[pabloevan]

My thoughts also.

I need it for the active cell. Thx

 

[My Aswer Is This]

Did my script toggling cell color do what you want?
Except for the Range?

I need it for the active cell. Thx

 

[My Aswer Is This]

Try this:
Applies to active cell

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

 

[pabloevan]

Try this:
Applies to active cell

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.