Formatting Cells Dependent on Contents

[Patrick Molloy]

You are limited to 56 colors in a worksheet.

Add a Change event to the sheets code page - right click
on the sheet tab & select View Code

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = GetColor(Target.Value)
End Sub

Now that you're in the IDE, add a standard module with
this code:


Function GetColor(sText As String) As Long
Dim cl As Long
On Error Resume Next
cl = Application.WorksheetFunction.VLookup(sText,
ThisWorkbook.Names("ColorList").RefersToRange, 2, False)
Err.Clear
On Error GoTo 0
GetColor = cl
End Function


add another worksheet.
in column A add the text list that you want and in B the
corresponing index - must be 0 to 56
range name the list ColorList

When you enter something on your first sheet, the change
event sets the cells color depending on the return value
from the function. The event handler passes the cell's
new value to the function.

The function simply uses the vlookup function to match
the text in A and get a numbet from B. Unfortunately the
VLookUp function generates an error condition if there's
no match, so our code skips this.


Simple hey?
If anyone wants my workbook, email me directly

Patrick Molloy
Microsoft Excel MVP