VBA conditioanl formatting a range

[austin350s10]

Aside from the excel conditioanl formatting is there a VBA script that I can run that will check a large range of cells. If a cell in the range equals TRUE then that cell is changed to a red background. If a cell in the range equals FALSE then nothing.

The only reason this needs to be a VBA script is because I only want this change to occur only when the user clicks a button not all the time.

 

[VoG]

With a button from the Controls toolbar try

Private Sub CommandButton1_Click()
Dim c As Range
Me.UsedRange.Interior.ColorIndex = xlNone
For Each c In Me.UsedRange
    If c.Value = True Then c.Interior.Color = vbRed
Next c
End Sub

 

[jasonb75]

Does it have to be a button? If you use a checkbox you could do it with conditional formatting by using the checkbox control cell as criteria.

Basic formula structure would be something like

=and(your criteria, control cell = true)

 

[austin350s10]

With

Private Sub CommandButton1_Click()
Dim c As Range
Me.UsedRange.Interior.ColorIndex = xlNone
For Each c In Me.UsedRange
    If c.Value = True Then c.Interior.Color = vbRed
Next c
End Sub</pre>

How do I set the range this script looks at to something else like: AG167:AG407

 

[austin350s10]

Maybe I should word this a different way. The range of cells I want to run this script on is:

Range("AG167:AG407")

If a cell in the range equals TRUE that cell is changed to a red background. If a cell in the range equals FALSE then do nothing at all and keep the original bgcolor.

Is this possible using the above code?

 

[jasonb75]

Haven't tested this,

Private Sub CommandButton1_Click()
Dim c As Range
Me.Range("AG167:AG407").Interior.ColorIndex = xlNone
For Each c In Me.Range("AG167:AG407")
If c.Value = True Then c.Interior.Color = vbRed
Next c
End Sub

but it looks right.

 

[austin350s10]

Perfect thanks a ton!!!