1

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
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
DMatt d20Gamer
  • 73
  • 5
  • 2
    `target` is an undeclared variable. `"M3, 03:Z3"` is not a valid address. `.Color`, not `.ColorIndex`, should be used with `RGB`. – BigBen May 02 '22 at 19:34
  • I appreciate the feedback. I am not sure how to list the cells I wish to target as a valid address, or declared variable. I am attempting to skip the cell "N3" in the row range of M3 through Z3, while including all other cells in that range as the target. – DMatt d20Gamer May 02 '22 at 21:09

1 Answers1

1

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • With the last issue solved, I am wondering how to add a second function onto the above code to add text to cell J3 using the same color references as above. If c.Interior.Color = RGB(252, 228, 214) Then .Interior.ColorIndex = 6 ADD> .Range("J3").Value = "Text Here" (**Problem: Range is already defined) and ElseIf c.Interior.ColorIndex = 6 Then .Interior.Color = RGB(252, 228, 214) ADD> .Range("J3").Value = "" (**Problem: Range is already defined) Without causing a loop reference to the range of cells mentioned above. – DMatt d20Gamer May 02 '22 at 23:22
  • See edit above. – Tim Williams May 02 '22 at 23:30
  • Tim Williams, you did it again. Exactly what I needed. Thank you, your help has been invaluable. I really appreciate the help you have given me today. – DMatt d20Gamer May 02 '22 at 23:48