The other users would need to have the add-in installed on their machines.

You could get away with more than 3 or 4 conditions if you used event code
behind the worksheet.

Similar to this which operates on data entry into column D

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "GO": Num = 10 'green
Case Is = "BUY": Num = 1 'black
Case Is = "SELL": Num = 5 'blue
Case Is = "DOG": Num = 7 'magenta
Case Is = "EAR": Num = 46 'orange
Case Is = "FOOT": Num = 3 'red
End Select
'Apply the color
With rng
.Interior.ColorIndex = Num
.Font.ColorIndex = Num
End With
Next rng
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord


On Wed, 2 May 2007 17:03:00 -0700, hopebear
wrote:

Thank you so very much Gord. Lots of information on the links you provided.
I use Excel 2003. I need 6+ conditions.

If I put the Add-in (to get say 10 conditions/colors) on my own system, then
put the Excel file on a shared drive for others to edit. Will all the other
users see the 10 colors using their own Excel 2003? or do all other users
need the Add-In module to see the 10 conditions/colors?

Otherwise, I will need to fit the 6 conditions/colors into the limited 4.

Thanks again.

Jane
----------

"Gord Dibben" wrote:

It is called FormatConditional Formatting.

In Excel versions 97 through 2003 you can have have 3 conditions......4 if you
count the default. 2007 has many more.

For a list of the colors in the 56 color palette see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/colors.htm

Bob Phillips has an add-in that allows up to 30 condtions.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 2 May 2007 13:39:02 -0700, hopebear
wrote:

I am not sure what this function is called. The cell can be programmed that
if the result is 0 or 1 or 2 or 3 - color will display instead of the number.
So 0 is black, red, green, yellow. sample formula is IF(G62="GO", 1,
IF(G62="BUY", 2, IF(G62="SELL", 3,0))) then the cell will show the
corresponding color (0,1,2,3 - black, green, yellow, red).

My questions a
1. what is this called? so I can search in 'HELP'.

2. are there more than 4 colors (B,G,R,Y)? I like few more and their
corresponding value. How do I go about finding this out? I am looking for
navy blue (and maybe more later).

Thank you so very much.