VBA Have cell color change when adding/removing check mark

[Blooze]

I'm trying to combine code that will add/remove a check mark and change the color to yellow when the check mark is added and change it to no fill when removed. I know virtually nothing about VBA code though.



I have this code that adds/removes the checkmark fine:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("A7:A1000, J7:J1000,k7:k1000,l7:l1000")) Is Nothing Then

Application.EnableEvents = False

If ActiveCell.Value = ChrW(&H2713) Then

ActiveCell.ClearContents

Else

ActiveCell.Value = ChrW(&H2713)

End If

Cancel = True

End If

Application.EnableEvents = True

End Sub

I have this code which seems to work to change the color in another file (it's obviously not on double click though) . I would like to have the color change work with the double click as well.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)





'if cell fill is Blank, change to Yellow

If Target.Interior.ColorIndex = xlNone Then

Target.Interior.Color = RGB(255, 255, 0)

Exit Sub

End If



'if cell fill is Gray, change to Yellow

If Target.Interior.Color = RGB(242, 242, 242) Then

Target.Interior.Color = RGB(255, 255, 0)

Exit Sub

End If



'If cell fill is Yellow, remove fill color

If Target.Interior.Color = RGB(255, 255, 0) Then

Target.Interior.ColorIndex = xlNone

End If



End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   If Not Intersect(Target, Range("A7:A1000, J7:J1000,k7:k1000,l7:l1000")) Is Nothing Then
      Application.EnableEvents = False
      If Target.Value = ChrW(&H2713) Then
         Target.Value = ""
         Target.Interior.ColorIndex = xlNone
      Else
         Target.Value = ChrW(&H2713)
         Target.Interior.Color = vbYellow
      End If
      Cancel = True
   End If
   
   Application.EnableEvents = True

End Sub

 

[Blooze]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   If Not Intersect(Target, Range("A7:A1000, J7:J1000,k7:k1000,l7:l1000")) Is Nothing Then
      Application.EnableEvents = False
      If Target.Value = ChrW(&H2713) Then
         Target.Value = ""
         Target.Interior.ColorIndex = xlNone
      Else
         Target.Value = ChrW(&H2713)
         Target.Interior.Color = vbYellow
      End If
      Cancel = True
   End If
  
   Application.EnableEvents = True

End Sub

That works perfectly! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.