Conditional Formatting - need more options for colour changi

[chrisaustralia]

hi, i know in conditional formatting, it only allows 3 values for changing the cell colour..I need to be able to put in 6 conditions for colours. If there a formula to do this? Thanks.

 

[PaddyD]

hi - welcome to the board!

describe your conditions.

 

[chrisaustralia]

Ok, well i have 6 sets of conditions. 1. GI = Green, 2. INV = Blue, 3. Super =Yellow 4. Life =Purple, 5 Wrap = Red, 6: Corporate=Light blue.
In conditional formatting, it only allows the first 3 sets to change colours if the cell equals one of those conditions. Make sense?!

Thanks!!

 

[Smitty]

Welcome to the Board!

What specific range do your conditions apply to?

Smitty

 

[chrisaustralia]

Just to one column. F3, to F135

Thanks.

 

[Smitty]

This ougt to do it:

<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:#007F00">'   Multiple Conditional Format</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#007F00">'   Adjust Format range to suit</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("F3:F135")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Adjust conditions to suit</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
            <SPAN style="color:#00007F">Case</SPAN> "GI"
                Target.Interior.ColorIndex = 50  <SPAN style="color:#007F00">'   Green</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> "INV"
                Target.Interior.ColorIndex = 3     <SPAN style="color:#007F00">'   Blue</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> "Super"
                Target.Interior.ColorIndex = 6   <SPAN style="color:#007F00">'   Yellow</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> "Life"
                Target.Interior.ColorIndex = 13   <SPAN style="color:#007F00">'   Purple-ish</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> "Wrap"
                Target.Interior.ColorIndex = 5    <SPAN style="color:#007F00">'   Red</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> "Corporate"
                Target.Interior.ColorIndex = 37   <SPAN style="color:#007F00">'   Light Blue</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

 

[chrisaustralia]

Where do you get the Color Index numbers from?

Thanks Heaps for the reply. Works like a treat.

 

[Smitty]

Where do you get the Color Index numbers from?

<font face=Tahoma><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> GetColors()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#007F00">'   Add Colors and their related Indices to a blank sheet</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 56
            Cells(x, 1) = x
            Cells(x, 2).Interior.ColorIndex = x
        <SPAN style="color:#00007F">Next</SPAN> x
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or you can simply record applying different colors to a cell.

HTH,

Smitty