VBA check box used to change color of a cell not working

[cjt]

I have a button on my spreadsheet that runs a macro to check if several check boxes are checked or not. If they are it should turn a designated cell green. For some unknown reason the code which has worked for me before is not working and no cells change color whether or not the check box is actually checked. Basically when I run the code nothing happens at all here is the code I'm using:

If ActiveSheet.CheckBoxes(1).Value = True Then ActiveSheet.Range("B6").Interior.ColorIndex = 4
If ActiveSheet.CheckBoxes(1).Value = False Then ActiveSheet.Range("B6").Interior.ColorIndex = 0

[snb]

Can you please add code tags around VBA code ?

This suffices

Sub M_snb()
ActiveSheet.Range("B6").Interior.ColorIndex = 4*abs(ActiveSheet.CheckBoxes(1).Value)
end sub

[Zack Barresse]

Without all of your code it's hard to determine exactly why it's not working. One thing I would suggest is you replace ActiveSheet with an actual worksheet reference, i.e. Sheets("Sheet1").

[cjt]

I will definitely use the code tags in the future, I'm new here thank you for that. So the code you gave me only worked when checked but gave an error if the checkbox wasn't checked. This bit of code finally got the result I needed:


If ActiveSheet.CheckBoxes(1).Value = True Then ActiveSheet.Range("B6").Interior.ColorIndex = 4 * Abs(ActiveSheet.CheckBoxes(1).Value)
Else: ActiveSheet.Range("B6").Interior.ColorIndex = 0
End If

Thanks for your help!

[cjt]

So I am very confused because both of the pieces of code that I posted above worked initially and then after a couple of tries no longer function. There has been no change in the code but they simply do nothing now when run. Does anyone know what could be causing this problem?

[Zack Barresse]

Ah, ok, a False, or unchecked value, will not be 0, so the code snb posted will need a slight adjustment...


Sub M_snb()
ActiveSheet.Range("B6").Interior.ColorIndex = 4 * Abs(WorksheetFunction.Max(ActiveSheet.CheckBoxes(1).Value, 0))
End Sub

Edit: You do know you can do this without code by using conditional formatting, right? Just tie the conditional formatting of B6 to the LinkedCell value.

HTH

[cjt]

Ah that works great thanks guys! I do need to use code since this is going into a report that itself is generated by VBA code and will be generated over and over again to produce many reports of the same format all of which need to have this property.