Conditional formatting using VBA

**kborgers** · 03-02-2010, 04:55 AM

I must confess that I’m really getting out of my depth here. I am VERY new to VBA and I just have no idea how to solve an issue so any suggestions more than welcome! I have the following syntax;

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("B4:G5") = format this range to fill the cells with light grey
ElseIf CheckBox1 = False Then
Range("B4:G4") = do nothing
End If
End Sub

As you can see, I have created a simple check box that when checked should ‘grey out’ or fill in the range of cells light grey. If unchecked, then it should just leave the information in those cells ‘as is’, which is a combination of user input and formula.

**MarMo** · 03-02-2010, 08:40 AM

Hi,

I adapted your code with the INTERIOR.COLORINDEX property to set the backgroudcolor of a cell or cells .

Private Sub CheckBox1_Click()
Dim Checkbox1
Checkbox1 = True
If Checkbox1 = True Then
Range("B4:G5").Interior.ColorIndex = 15
'15 will give you the grey color
Else
'clear the backgroud color with xlNone
Range("B4:G5").Interior.ColorIndex = xlNone
End If
End Sub

You can find the colorindex numbers at this website.
http://www.mvps.org/dmcritchie/excel/colors.htm

**kborgers** · 03-02-2010, 08:51 AM

Hey Marmo, many thanks indeed for your suggestion which works brilliantly until I uncheck the box. Having done so, the grey background remains whereas I need it to revert back to the original state. Should I have mentioned this?

**MarMo** · 03-02-2010, 08:58 AM

Originally Posted by kborgers

Hey Marmo, many thanks indeed for your suggestion which works brilliantly until I uncheck the box. Having done so, the grey background remains whereas I need it to revert back to the original state. Should I have mentioned this?

Hi ,

I saw it too late and I 've changed the above code into

Else
'clear the backgroud color with xlNone
Range("B4:G5").Interior.ColorIndex = xlNone

Hope this helps.

**kborgers** · 03-02-2010, 09:06 AM

Is this not the same as before?

**MarMo** · 03-02-2010, 09:22 AM

Hi kborgers ,

I attached a simple example with
1) a checkbox on the worksheet
2) a checkbox on a userform.

Both checkboxes work and will change the backgroundcolor when checked or unchecked.
Check out the VBA code
If you still got questions , me or others will be here to help.

Rgds
MarMo

**kborgers** · 03-02-2010, 10:16 AM

As you can see from the attached, I played around with your suggestion, made some changes and it still works fine. However, when I copy the syntax into my 'real' sheet, it does not work when unchecking the box! Bizarre and ever so frustrating! Many thanks once more for your kind help - I'll keep trying!