What to add if i click a button again

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
gazza_606,

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

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Aug 19, 2014
Messages
22

ADVERTISEMENT

Thanks, i have used this and it works :) thanks again
 

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Try....

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,879
Latest member
Aussie_Excel_Wanna_Be
Top