VBA Express : Excel - Conditional Formatting (More Than Three)

Excel

Conditional Formatting (More Than Three)

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

Conditional Formatting is limited to three different formats (four if you count the actual formatting of the cell). With VBA we can overcome this limitation and have as many conditions as are needed.

Discussion:

You have an Excel file and you want to have the cell's formatting change when new values are added. You want to have more than three conditions so you cannot use Excel's Conditional Formatting. With VBA a Worksheet_Change event can apply all the conditions that you want. In the example only the interior color and bold properties are changed, however, you can apply any formatting that you want.

Code:

instructions for use

Option Compare Text 'A=a, B=b, ... Z=z Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim Rng1 As Range On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False Case "Tom", "Joe", "Paul" Cell.Interior.ColorIndex = 3 Cell.Font.Bold = True Case "Smith", "Jones" Cell.Interior.ColorIndex = 4 Cell.Font.Bold = True Case 1, 3, 7, 9 Cell.Interior.ColorIndex = 5 Cell.Font.Bold = True Case 10 To 25 Cell.Interior.ColorIndex = 6 Cell.Font.Bold = True Case 26 To 99 Cell.Interior.ColorIndex = 7 Cell.Font.Bold = True Case Else Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False End Select Next End Sub

How to use:

Open Excel.
Right click on the sheet tab you want to apply the code to and select "View Code".
Paste the code there.
Close VBE (Alt + Q or press the X in the top right hand corner).
Type or paste new values into the cells. If they meet the conditions specified in the code, the formatting will be applied.
Change the code as needed. It demonstrates how to check for text strings and number values.

Test the code:

Refer to the "How To Use" section.
Download the attachment for a working example.

Sample File:

Conditional Formatting.ZIP 5.91KB

Approved by mdmackillop

This entry has been viewed 2887 times.