|
Q: |
I am using Excel 2003. I need to increase the
number of Conditional Formatting options available up to 5 or 6
options. Can you help me? |
|
|
|
|
A: |
Excel 2003 and earlier only allow up to 3
Conditional Formatting options, as I showed you in one of my
previous Tips & Tricks
videos. However, using a little bit of VBA (Visual Basic for
Applications) code, you can extend the maximum number of Conditional
Formatting options as high as you want. You're limited only by your
imagination.
|
|
|
 |
|
|
Here is the code that you need to insert into your spreadsheet. The free
video below explains exactly how to do this, step by step.
|
|
|
Private Sub
Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("B2:B8"))
If Not I Is Nothing Then
Select Case Target
Case 0 To 100: NewColor = 37 ' light blue
Case 101 To 200: NewColor = 46 ' orange
Case 201 To 300: NewColor = 12 ' dark yellow
Case 301 To 400: NewColor = 10 ' green
Case 401 To 600: NewColor = 3 ' red
Case 601 To 1000: NewColor = 20 ' lighter blue
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("F1:F1") = Range("F1:F1").Interior.ColorIndex
End Sub
|
|
|
We have a FREE video
tutorial that shows you how to do this step-by-step.
I also cover Conditional Formatting in much more detail
in my Excel 104
class, and I will be covering all of the VBA material in greater depth a
future lesson. Make sure you get on my mailing list to be notified when it's
completed. |
