is there any way to have more than 3 conditional formats or something like
doing it by formula?
thanks in advance

This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A10 and set the background color:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colors based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"qwerty" wrote:

is there any way to have more than 3 conditional formats or something like
doing it by formula?
thanks in advance

qwerty

John McGimpsey's site shows how to have up to 6 CF on font color per cell.

http://www.mcgimpsey.com/excel/conditional6.html

Otherwise a select case worksheet_change event is needed.

A couple of examples................

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


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("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each Rng In vRngInput
'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
Rng.Font.ColorIndex = Num
Next Rng
End Sub

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

Paste one of the above into that module.


Gord Dibben Excel MVP

On Wed, 16 Nov 2005 06:30:08 -0800, qwerty wrote:

is there any way to have more than 3 conditional formats or something like
doing it by formula?
thanks in advance