ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

Chad's Macro Mania
~~Chad K. Welch

Extended Conditional Formatting in Excel

One of the great features of Excel is the conditional formatting. With a quick glance at the spreadsheet you can notice certain areas of concern that may have been overlooked if not for the different cell formats. Unfortunately, Excel only permits three conditional formats for any cell. Often it is necessary to include more than four formats. (No that wasn’t a typo – with conditional formatting you can have four formats: the normal format and three conditions.  For example if a cell will have the value 1, 2, 3, or 4, and each value should have a different format, then apply a normal format for the value 1, and three conditional formats for 2, 3 and 4.)

With a little VBA you can create as many formats as you need.

Right click the worksheet tab where you will be adding the extended conditional formatting and choose 'View Code.'

Then,  in the Visual Basic Editor that opens choose 'Worksheet' in the left combo box and 'Change' in the right combo box. That will create the code-wrapper shown in the picture.

The next step (if you haven't already done it) is to determine the different formats that will be applied to each condition and the range(s) that the formats will be applied. I usually list the range(s) and formats in comments near the beginning of my code. For example:
 

Private Sub Worksheet_Change(ByVal Target As Range)

    'A1:A10

        '0-10   Background = red(3)

        '>10-20 Background = orange(44)

        '>20-30 Background = yellow(6)

        '>30-40 Background = green(4)

        '>40-50 Background = blue(5)

        '>50    Background = purple(21)

       

    'D1:D10

        '<0         Font = red(3)   background = white(2)

        '0-100      Font = white(2) background = black(1)

        '100-<500   Font = black(1) background = green(4)

        '500-<1000  Font = green(4) background = black(1)

        '>1000      Font = black(1) background = red(3)

 

End Sub

The numbers in parenthesis are the color indices relating to each color. In Excel you can pick from the 56 colors shown in this chart.

I only used font and background colors in my example, but you could also make the text bold or italic, add or remove borders, change the font or a myriad of other things. One common format that a lot of people ask about is blinking cells. Come back next month and I'll show you how to make the cell backgrounds blink different colors.

Once you've determined the different conditions it is just a matter of building the code to handle it. For the examples I chose, the code will be:

Private Sub Worksheet_Change(ByVal Target As Range)

    'A1:A10

        '0-10   Background = red(3)

        '>10-20 Background = orange(44)

        '>20-30 Background = yellow(6)

        '>30-40 Background = green(4)

        '>40-50 Background = blue(5)

        '>50    Background = purple(21)

       

    'D1:D10

        '<0         Font = red(3)   background = white(2)

        '0-100      Font = white(2) background = black(1)

        '100-<500   Font = black(1) background = green(4)

        '500-<1000  Font = green(4) background = black(1)

        '>1000      Font = black(1) background = red(3)

       

    Dim cel As Range

   

    For Each cel In Range("A1:A10").Cells

        If IsNumeric(cel.Value) And cel.Value <> "" Then

            If cel.Value >= 0 And cel.Value <= 10 Then

                cel.Interior.ColorIndex = 3

            ElseIf cel.Value > 10 And cel.Value <= 20 Then

                cel.Interior.ColorIndex = 44

            ElseIf cel.Value > 20 And cel.Value <= 30 Then

                cel.Interior.ColorIndex = 6

            ElseIf cel.Value > 30 And cel.Value <= 40 Then

                cel.Interior.ColorIndex = 4

            ElseIf cel.Value > 40 And cel.Value <= 50 Then

                cel.Interior.ColorIndex = 5

            ElseIf cel.Value > 50 Then

                cel.Interior.ColorIndex = 21

            Else 'default conditions

                cel.Interior.ColorIndex = 0

                cel.Font.ColorIndex = 1

            End If

        Else

            cel.Interior.ColorIndex = 0

            cel.Font.ColorIndex = 1

        End If

    Next

                

    For Each cel In Range("D1:D10").Cells

        If IsNumeric(cel.Value) And cel.Value <> "" Then

            If cel.Value < 0 Then

                cel.Font.ColorIndex = 3

                cel.Interior.ColorIndex = 0

            ElseIf cel.Value < 100 Then

                cel.Font.ColorIndex = 2

                cel.Interior.ColorIndex = 1

            ElseIf cel.Value < 500 Then

                cel.Font.ColorIndex = 1

                cel.Interior.ColorIndex = 4

            ElseIf cel.Value < 1000 Then

                cel.Font.ColorIndex = 4

                cel.Interior.ColorIndex = 1

            Else '>=1000

                cel.Font.ColorIndex = 1

                cel.Interior.ColorIndex = 3

            End If

        Else

            cel.Font.ColorIndex = 1

            cel.Interior.ColorIndex = 0

        End If

    Next

 

End Sub

Send me an idea I can use for this column and if you are the first to submit the idea I'll send you $5.00. Send it to chad@welchkins.com and just make sure to put "Idea for ABC" in the subject line.

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for more information.