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]

Sorry , is there a proble

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


franko

Should work - what are you seeing?

Also, is your data case sensative?

 

[franko]

No, as far as I can see
is working

thanks
franko

 

[just_jon]

If you need a=A, etc then change this line

Select Case c.Value

to

Select Case UCase(c.Value)

 

[franko]

Good to know...

thanks
franko.

 

[franko]

Hi I would like to know how to limit
the conditions into a Range for instance I need them on Range

Beginning in D10 and ending BJ46
or better D:BJ from ROW 10 to 46

as well as I would need to have in those ranges
columns G,L,Q,V,AA,AF,AK,AP,AU,AZ,BE,BJ
condition for background colour

Selection.Interior
.ColorIndex = 35 green

Selection.Font.ColorIndex = 5 blue

is it possible in one code ???

thanks
franko

 

[just_jon]

Hi I would like to know how to limit
the conditions into a Range for instance I need them on Range

Beginning in D10 and ending BJ46
or better D:BJ from ROW 10 to 46

as well as I would need to have in those ranges
columns G,L,Q,V,AA,AF,AK,AP,AU,AZ,BE,BJ
condition for background colour

Selection.Interior
.ColorIndex = 35 green

Selection.Font.ColorIndex = 5 blue

is it possible in one code ???

thanks
franko

Confused a bit - in D10:BJ46 you want the current code, except in columns G,L,Q,V,AA,AF,AK,AP,AU,AZ,BE,BJ you want a different set of conditions?

What are the criteria for the other conditions?

 

[franko]

Hi Jon,

sorry forgot to mention the criteria is
in those ranges in columns there will always be entered a text which is in font blue and background colour light green ( for our events) actually

sorry for that I have discovered that later on as i tried to write something
in .....

thanks
franko

 

[just_jon]

Try -

<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, rng1 <SPAN style="color:#00007F">As</SPAN> Range, rng2 <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> rng1 = Union(Range("G:G"), Range("L:L"), Range("Q:Q"), Range("V:V"), Range("AA:AA"), _
                 Range("AF:AF"), Range("AK:AK"), Range("AP:AP"), Range("AU:AU"), Range("AZ:AZ"), _
                 Range("BE:BE"), Range("BJ:BJ"))
<SPAN style="color:#00007F">Set</SPAN> rng2 = Range("D:BJ")
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Target.Cells
    <SPAN style="color:#00007F">If</SPAN> c.Row >= 10 And c.Row <= 46 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(c, rng1) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            c.Interior.ColorIndex = 35 <SPAN style="color:#007F00">'green</SPAN>
            c.Font.Color = 5  <SPAN style="color:#007F00">'blue</SPAN>
        <SPAN style="color:#00007F">ElseIf</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(c, rng2) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            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">Else</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Finish:
<SPAN style="color:#00007F">Set</SPAN> rng1 = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rng2 = <SPAN style="color:#00007F">Nothing</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[HowdyTX]

This is very similiar to what I need, but the cells are changed via a formula and not data input. What would the macro be in this scenario?

Also, is there a cross reference list for the 56 color palette? For example 3 = red, 5 = blue, etc. I actually need the color written out in words and not just the palette shown. I am completely color blind! I have only been able to find references with color blocks and their corresponding numbers. That's great for most, but I still haven't a clue as to what color the number relates to!

Numbers just are black and white anymore!

Thanks - TX

 

[just_jon]

This is very similiar to what I need, but the cells are changed via a formula and not data input. What would the macro be in this scenario?

Also, is there a cross reference list for the 56 color palette? For example 3 = red, 5 = blue, etc. I actually need the color written out in words and not just the palette shown. I am completely color blind! I have only been able to find references with color blocks and their corresponding numbers. That's great for most, but I still haven't a clue as to what color the number relates to!

Numbers just are black and white anymore!

Thanks - TX

I've a bit of trouble w/ colors, too - blues-to-greens, etc.

But the only number-to-name I've seen is this from the VBA Help:

QBColor Function
      

Returns a Long representing the RGB color code corresponding to the specified color number.

Syntax

QBColor(color)

The required color argument is a whole number in the range 0–15.

Settings

The color argument has these settings:

Number Color Number Color 
0 Black 8 Gray 
1 Blue 9 Light Blue 
2 Green 10 Light Green 
3 Cyan 11 Light Cyan 
4 Red 12 Light Red 
5 Magenta 13 Light Magenta 
6 Yellow 14 Light Yellow 
7 White 15 Bright White 



Remarks

The color argument represents color values used by earlier versions of Basic (such as Microsoft Visual Basic for MS-DOS and the Basic Compiler). Starting with the least-significant byte, the returned value specifies the red, green, and blue values used to set the appropriate color in the RGB system used by Visual Basic for Applications.

As formula changes do not trigger a change event, you'd need to use a worksheet calculate event, I'd think.