Toggle Code to format-unformat range of cells

**Zimbo** · 02-09-2008, 12:35 AM

Hi All,

I am trying to combine the 2 macro strings below.
This is to change the font colour in a range of cells so as to hide the inputs and then unhide them when toggle button is unclicked.

Any recommendations on a shorter line of code would be much appreciated.

Thanks


Private Sub ToggleButton2_Click()
If ToggleButton2.Value = True Then

    Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    With Selection.Font
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlNone
      ' or .ColorIndex = 2
        
    End With
End Sub




Private Sub ToggleButton2_Click()
If ToggleButton2.Value = False Then

    Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    With Selection.Font
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        
    End With
End Sub

**mikerickson** · 02-09-2008, 01:27 AM

This should do it

Private Sub ToggleButton2_Click()
Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row).Font.ColorIndex = IIf(ToggleButton2.Value, 2, xlAutomatic)
End Sub

I was getting errors trying to set the .ColorIndex to xlNone, so I used 2 (white).

If there were more properties to be toggled, an If Else EndIf would be a good way to go.