I need to conditionally format a cell in 6 colors. So if the cell contains r,
it gets red, g it gets green, etc for blue, grey, yellow.

I only see 3 possible conditional formats (or 4 with the default).

Is there anyway around this? Can I make a formula that would copy some other
cell, formatting and all, to the cell?

Side question: Is it possible to put a picture behind the cells so it shows
up on the screen (i.e. not header or footer.)

Sterling

You can use event code to format the cells.

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 = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module. Adapt to suit your conditions.

Also note it is designed to operate on Column D only. You can edit that by
changing D:D to whatever.

For your second question........do you mean a printable watermark?

If so, see Bob Phillips' site

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


Gord Dibben MS Excel MVP

On Fri, 6 Oct 2006 12:10:02 -0700, Sterling
wrote:

I need to conditionally format a cell in 6 colors. So if the cell contains r,
it gets red, g it gets green, etc for blue, grey, yellow.

I only see 3 possible conditional formats (or 4 with the default).

Is there anyway around this? Can I make a formula that would copy some other
cell, formatting and all, to the cell?

Side question: Is it possible to put a picture behind the cells so it shows
up on the screen (i.e. not header or footer.)