Home > Enterprise >  How to change color in a range of cells using an Axtive X control button?
How to change color in a range of cells using an Axtive X control button?

Time:05-03

I am attempting to use an Active X control button to change the background (fill) color of a range of cells when the button is clicked and then change it back to its original color when clicked again. The code I have returns errors. I am looking for a simple solution.

Basic Idea:

   Private Sub CommandButton3_Click()
    If Intersect(target, Range("M3, 03:Z3")) Is Nothing Then Exit Sub
        If target.Interior.ColorIndex = RGB(252, 228, 214) Then
            target.Interior.ColorIndex = 6
        ElseIf target.Interior.ColorIndex = 6 Then
            target.Interior.ColorIndex = RGB(252, 228, 214)
        End If
    End Sub

CodePudding user response:

Try this:

Private Sub CommandButton3_Click()
    Dim c As Range
    With Me.Range("M3,O3:Z3")
        Set c = .Cells(1)  'assuming all cells in the range of interest have the same color?
        If c.Interior.Color = RGB(252, 228, 214) Then
            .Interior.ColorIndex = 6
            Me.Range("J3").Value = "Some text"
        ElseIf c.Interior.ColorIndex = 6 Then
            .Interior.Color = RGB(252, 228, 214)
            Me.Range("J3").ClearContents
        End If
    End With
End Sub
  • Related