need conditional format for 6 conditions

you would have to do it with code...
are you only checking cell A1 and then coloring cell A1?

if so something like this possibly



let me know if that'll work or if you need more help

Search gives you tons of answers. Here's one:
http://www.mrexcel.com/board2/viewtopic.php?t=157755&highlight=multiple+conditional+format

For more than 3 conditions, you have to use a macro.

I believe this should work for you.

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Finish
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#007F00">'if more than 1 cell is changed or changed cell is not A1, exit macro</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Or</SPAN> Target.Address(0, 0) <> "A1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Finish

<SPAN style="color:#007F00">'otherwise, run following code to check value of A1 and _
change colors</SPAN>
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "C"
        Target.Interior.ColorIndex = 3 <SPAN style="color:#007F00">'red</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "H"
        Target.Interior.ColorIndex = 6 <SPAN style="color:#007F00">'yellow</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "N"
        Target.Interior.ColorIndex = 4 <SPAN style="color:#007F00">'green</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "S"
        Target.Interior.ColorIndex = 5 <SPAN style="color:#007F00">'blue</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "L"
        Target.Interior.ColorIndex = 1 <SPAN style="color:#007F00">'black</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "D"
        Target.Interior.ColorIndex = 15 <SPAN style="color:#007F00">'gray</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
        Target.Interior.ColorIndex = -4142 <SPAN style="color:#007F00">'no color</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

Finish: Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You have to put the code in the correct module, though. Right-click the sheet tab and select View Code. Paste this code into the window that appears. Then this code should run whenever a change is made on that sheet.

NOTE: This code will NOT run if the cells are being changed by formulas. You need a different macro for that.

Hi ,

does your macro code work for a whole worksheet
as it is used on severel columns in differrent rows and cells
so would be nice if it would recognise any value in several cells on any columns....

how does the macro start with usually it starts with
sub .. () didn`t see that on your posted code sorry for this poor question

regards
franko

Hi ,

your solution sounds good
could you give me an example of minimum 4 conditions in one formula
and I'll catch up hopefully to customize it for 6 conditions
even I will need to distribute this formula in any column needed on my worksheet


thanks
franko

Kristy posted the whole code for you...if you need anything else then post back...

ps if you need it for the whole sheet and not just A1 (let us know if not the whole sheet but maybe certain columns)....then change

If Target.Count > 1 Or Target.Address(0, 0) <> "A1" Then GoTo Finish

to

If Target.Count > 1 Then GoTo Finish

In the code that Kristy posted, change this line

If Target.Count > 1 Or Target.Address(0, 0) <> "A1" Then GoTo Finish

to this line

If Target.Count > 1 Then GoTo Finish

If you need to cover the possibility of changinf more than 1 cell at a time, then post back.

The macro is not run by you directly, but rather fires each time you change a cell.

Hi I tried the code below doesn't work
BTW need to look at whole sheet ....

and running XLS 2000 ...

thanks
franko



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 26/08/2005

On Error GoTo Finish
Application.EnableEvents = False

If Target.Count > 1 Then GoTo Finish

Select Case Target.Value
Case Is = "C"
Target.Interior.ColorIndex = 3 'red
Case Is = "H"
Target.Interior.ColorIndex = 6 'yellow
Case Is = "N"
Target.Interior.ColorIndex = 4 'green
Case Is = "S"
Target.Interior.ColorIndex = 5 'blue
Case Is = "L"
Target.Interior.ColorIndex = 1 'black
Case Is = "D"
Target.Interior.ColorIndex = 15 'gray
Case Else
Target.Interior.ColorIndex = -4142 'no color
End Select

Finish: Application.EnableEvents = True

End Sub

Hi,

could you please have a look I posted
what I have created didn't work

thanks franko