need conditional format for 6 conditions

[franko]

Does anybody know how to have more than 3 conditions
on one cell for instance I need 6 different colour conditions

so if conditions:

1 ) Cell Value in A1 = "C" colour needs to be in red
2 ) Cell Value in A1 = "H" colour needs to be in yellow
3 ) Cell Value in A1 = "N" colour needs to be in green
4 ) Cell Value in A1 = "S" colour needs to be in blue
5 ) Cell Value in A1 = "L" colour needs to be in black
6 ) Cell Value in A1 = "D" colour needs to be in gray

unfortunately I have discovered that there is a limitation of 3 conditions
so how can I add more conditions ????


any help is highly appreciated

thanks
franko

 

[just_jon]

Change this

Sub Macro1()

to this

Private Sub Worksheet_Change(Byval Target as Range)

Still have not answered if you plan to change >1 cell at a time.

 

[franko]

GREAT!

I got it

thanks to you all
and sorry for my poor knowledge it even took me a long time until I discovered how to use the "View Code" in VBA

thanks that's why I love this page
franko

 

[franko]

Hi ,

Yes there will be obviously a change of more than 1 cell
at a time as it takes the value of a different column which I copy
and pasty into a particular column however there is another problem
which comes up as the colour is changed to black in condition "L"
I need to have a 7th condition for the Font as than this needs to be in white
otherwise I wouldnt see the Letter "L" , no ?

can you help me how to do that without of distributing that with
conditional format on the commercial way ?

thanks
franko

 

[TheNoocH]

try changing this from

Case Is = "L"
Target.Interior.ColorIndex = 1 'black

to

Case Is = "L"
Target.Interior.ColorIndex = 1 'black
Target.Font.Color = vbWhite


PS....you may want to add

Target.Font.ColorIndex = 1 'black
to the end of the other case statements so that if a cell goes from L to somethign else it reverts back to black...

 

[franko]

PERFECT!!

I like that....

thanks again
franko

 

[just_jon]

<font face=Courier New><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)
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Finish
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Target.Cells
    c.Font.Color = vbBlack <SPAN style="color:#007F00">' Reset font color, in case the cell had been an "L" cell</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Value
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "C"
            c.Interior.ColorIndex = 3 <SPAN style="color:#007F00">'red</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "H"
            c.Interior.ColorIndex = 6 <SPAN style="color:#007F00">'yellow</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "N"
            c.Interior.ColorIndex = 4 <SPAN style="color:#007F00">'green</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "S"
            c.Interior.ColorIndex = 5 <SPAN style="color:#007F00">'blue</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "L"
            c.Interior.ColorIndex = 1 <SPAN style="color:#007F00">'black</SPAN>
            c.Font.Color = vbWhite
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "D"
            c.Interior.ColorIndex = 15 <SPAN style="color:#007F00">'gray</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            c.Interior.ColorIndex = -4142 <SPAN style="color:#007F00">'no color</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Finish:
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[TheNoocH]

just jon....nice code...wouldn't have thought of doing it that way...

 

[just_jon]

just jon....nice code...wouldn't have thought of doing it that way...

Y'know, I've started doing all my events that way - unless the ability to change >1 cell is something I want to restrict.

The For Each does not cost much extra in those cases where it is a single cell change, and it adds flexibility.

 

[franko]

Sorry , is there a proble

if I copy paste values at a the same time for more
cells ???


franko

 

[TheNoocH]

just jon....nice code...wouldn't have thought of doing it that way...

Y'know, I've started doing all my events that way - unless the ability to change >1 cell is something I want to restrict.

The For Each does not cost much extra in those cases where it is a single cell change, and it adds flexibility.

just saved it with my sample files...i'm learning event code so all the nice tricks help....

PS...Franco...do you want this to work for upper and lower case or just uppercase?