Formatting Cells Dependent on Contents
 

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






-----Original Message-----
Is their an easy piece of code which could be used on an
entire worksheet to format the cells dependent on their
content. I.e if a cell contains the text 'house' then
the
cell has a red background etc. for about 50 conditions.
This is similar to conditional formating but i need more
than 3 conditions and the code should update the colour
of
the cell if the content changes?

I am sure this must be fairly simple, but when i have
aksed in the past the code i received was far too
complex
and did not update? I am fairly new to the code side of
Excel so please keep any examples simple.

Danny.
.