ExcelBanter - Conditional Formatting in 6 colors

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.)