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