What to add if i click a button again

[gazza_606]

I currently have a button in an excel sheet that reverses a selection of data from positive to negative or visa versa. With the macro the font in the button changes from red to green to indicate that the button has been pressed. What i need to add is to change the font back to red if it is pressed again and needs to keep changing everytime the button is pressed.

Here is the current macro
Sub Reverse()
'
' Reverse Macro
'
ActiveSheet.Unprotect
Range("B20:B36").Select
Selection.Copy
Range("W72").Select
ActiveSheet.Paste
Range("X72").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]"
Range("X72").Select
Selection.AutoFill Destination:=Range("X72:X88"), Type:=xlFillDefault
Range("X72:X88").Select
Range("X72").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*R72C18"
Range("X72").Select
Selection.AutoFill Destination:=Range("X72:X88")
Range("X72:X88").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-42
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Shapes.Range(Array("Button 68")).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 4
End With
ActiveSheet.Protect
End Sub

Is there bit of code that can be added to keep switching between the two colours

Thanks

 

[Snakehips]

gazza_606,

Is the sole purpose of this code to toggle the values in B20:B36 from negative to positive?

 

[gazza_606]

Hi Snakehips, yes that is all this needs to do but i just need to know if they have been reversed and i thought the easiest way is to change the font colour.

 

[Snakehips]

This should switch between positive and negative and black and green button font.
Up to you to set the initial state / colour combination, after which it will toggle.

Sub Pos_Neg()
Application.ScreenUpdating = False
For Each cell In Range("B20:B36")
cell.Value = cell * -1
Next cell
With ActiveSheet.Shapes("Button 68").TextFrame.Characters.Font
If .ColorIndex = 4 Then
.ColorIndex = 1
Else
.ColorIndex = 4
End If
End With
End Sub

Hope that helps.

 

[gazza_606]

ADVERTISEMENT

Thanks, i have used this and it works thanks again

 

[gazza_606]

Hi, I am just looking into this again and have a quick question. although the values that need reversing are in B20:B36 this is the maximum case, sometimes i just need B20:B31, how do i only reverse the cells that are populated?
Thanks

 

[Snakehips]

Try....

Sub Pos_Neg()
Application.ScreenUpdating = False
For Each cell In Range("B20:B36")
If Not cell = "" Then cell.Value = cell * -1
Next cell
With ActiveSheet.Shapes("Button 68").TextFrame.Characters.Font
If .ColorIndex = 4 Then
.ColorIndex = 1
Else
.ColorIndex = 4
End If
End With
End Sub

 

[gazza_606]

Cheers, that's perfect.