Dan

For 10 numbers you would have to use VBA......event code is best.

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("A1"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
'add more cases here as above
End Select
'Apply the color
Range("H1").Interior.ColorIndex = Num
Next rng
End Sub

Just add more Cases and colors using the example above.

To see what the colorindexes are visit David McRitchie's site

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

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

Copy/paste the code into that sheet module.

You can add the Cases at that point.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 12:47:18 -0800, danielocope
wrote:

Hi All
I must be thick headed; I have looked through most of the posts here for
help and some come very close to answering my question, or maybe it’s just me!

I have the three conditional formatting settings in cell H1 based on the
value of cell A1. Please note there is no value in H1, no number or letter,
just the pattern color. I was hoping not to / do not want to use VB editor to
create some master code for the work sheet. I just want to use a FORMULA in
H1 to change the cell (H1) color based on the number value in cell A1.

Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7"))
(Numbers 1 – 10 would each have a different color, 1-red, 2-blue, 3-green,
and so on)

Now for the thick headed part, if a formula can not change a cell color is
there ANY other way to do this other than some code sheet, VB editing,
monster in Excel 2003?

Thanks for taking the time to look this over, and if I need to wrestle with
the code monster will you all be there to guide me through, PLEASE?
Dan