excelvbaactivexexcel-2016

Adding Horizontal and/or Vertical borders with VBA

When a checkbox is unchecked E4:F11 is hidden.
When it is checked the cells are visible and formatted.

I need more.

The first thing is this code creates a border around the range, which I do want.
Is there was a way to do horizontal and/or vertical borders on a certain range?
For example on the screen shot below, I want thin borders on the bottom of E5:F5, bottom of E7:F7 and the bottom of E9:F9 and still keep the thick borders on the whole range (E4:F11).

Second thing; is there was a way to only change the colors of some of the cells.
For instance on this screenshot I want E5, E7, E9, and E11 white filled (the rest the red color).

I am using ActiveX Checkboxes and ActiveX buttons on other parts of the sheet.

This is for work so I cant upload the sheet but I can answer questions and upload more screenshots if needed.

Private Sub CheckBox5_Click()
    With ActiveSheet
        If .Range("E4").Interior.Color = vbWhite Then
            
            With .Range("E4:E11", "F5:F11")
                .Interior.Color = RGB(220, 86, 65)
                .Font.Color = RGB(0, 0, 0)
            End With
          
            Range("E4:E11", "F5:F11").BorderAround _
              LineStyle:=xlContinuous, _
              Weight:=xlThick, _
              Color:=RGB(0, 0, 0)
          
        Else
          
            With .Range("E4:E11", "F5:F11")
                .Font.Color = RGB(255, 255, 255)
                .Interior.ColorIndex = xlNone
                With .Borders
                    .LineStyle = xlNone
                End With
            End With
          
        End If
      
    End With
End Sub

Screenshot
enter image description here

Solution

  • Toggle Cell Formatting

    Private Sub CheckBox5_Click()
    
    Application.ScreenUpdating = False

    With ActiveSheet
        If .Range("E4").Interior.ColorIndex = xlNone Then
            With .Range("E4:F11")
                .EntireRow.Hidden = False ' ?
                .Interior.Color = RGB(220, 86, 65)
                .Font.Color = vbBlack
                With .Range("E5,E7,E9,E11")
                    .Interior.Color = vbWhite
                End With
                .BorderAround xlContinuous, xlThick, vbBlack
                .Rows(2).Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Rows(4).Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Rows(6).Borders(xlEdgeBottom).LineStyle = xlContinuous
            End With
        Else
            With .Range("E4:F11")
                .Interior.ColorIndex = xlNone
                .Font.Color = vbWhite
                .Borders.LineStyle = xlNone
                .EntireRow.Hidden = True ' ?
            End With
        End If
    End With

    Application.ScreenUpdating = False

End Sub