Excel Conditional Formatting with More Than Three Conditions 28

[DeLaMartre]

I have a spreadsheet in which I want Excel to apply conditional formatting to cells with seven different conditions, (different colors for each of seven numbers, "1"=blue, "2"=red, "3" =yellow, "4"=green, etc). Unfortunately, Excel limits me to three. I would be most grateful for any help.

 

[acron]

Conditional Formatting cannot manage more that 3 conditions, so you must resort to VBA. How to approachh that best depends on how the values of the range in question are updated. Presumably it is a result of formulas, in which case trapping th esheet calculate event should assist. The following code does that :

Private Sub Worksheet_Calculate()
Dim oCell As Rang
    For Each oCell In Range("A1:A20")
        Select Case oCell.Value
             Case Is < 1
                 oCell.Interior.ColorIndex = xlNone
             Case Is = 1
                 oCell.Interior.ColorIndex = 5
             Case Is = 2
                 oCell.Interior.ColorIndex = 3
             Case Is = 3
                 oCell.Interior.ColorIndex = 6
             Case Is = 4
                 oCell.Interior.ColorIndex = 4
             Case Is = 5
                 oCell.Interior.ColorIndex = 7
             Case Is = 6
                 oCell.Interior.ColorIndex = 15
             Case Is = 7
                 oCell.Interior.ColorIndex = 40
             Case Is > 7
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

That code must be placed in the codemodule od teh actual sheet you are working with. To that select the sheet in question, right click on the sheet tab and select View Code. You can then place code above in the resultantt code pane. You will need to change the range references (I have used A1:A20 in the example), to suit the range you are working with. You can also change the values applied to the color index to suit your own requirements.

AC

 

[DeLaMartre]

Perfect! You saved me a great deal of grief with this solution. Your help is very much appreciated, Acron! - Bob

 

[acron]

You are welcome. I supposed you must have noticed

[tab]Dim oCell As Rang,

which should read

[tab]Dim oCell As Range

Sorry about that.

AC

 

[DaleWatson123321]

DeLaMartre,

Psssssssssst.... you "forget to issue a "STAR".

It seems like I'm "one of the few" who bothers to point out to newcomers that the practice of "issuing STARS" here in Tek-Tips is the "proper" method of showing appreciation and recognition for the time contributors take (probably out of a BUSY schedule) to help resolve problems.

The ONLY form of "payment" is the "STARS", so PLEASE show this form of appreciation and recognition.

Issuing a STAR is real easy. You only have to click on the "Click here to mark this post as a helpful or expert post! - located in the lower-left-corner of the contributor's posting.

I'm told that other Tek-Tips "browsers" like to use the STARS as a form of "beacon" to guide them to useful threads which probably contain useful tips or solutions to similar problems, or they just want to collect such useful information for a "library".

Because acron’s contribution is a “worthy” one, I’ll go ahead and issue him a STAR, and if you would like to give him a SECOND STAR, that would also be fitting. “Acron” has probably “missed out” in the past in getting a STAR for a contribution where the recipient only gave a “thanks” and no STAR.

I hope you find this information useful. But PLEASE, do NOT consider this more than just a "reminder" - i.e. PLEASE do NOT issue a STAR for my posting. I want to EARN stars in the normal way. Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DeLaMartre]

Thanks for educating this "newbie", Dale, (and my apologies, Acron). As a teacher, I also appreciate someone who takes the time to educate those of us who need it!

Take care,

Bob

 

[euskadi]

I'd like to throw out a non-VBA alternative for those who come across this issue and who only have 5 criteria to deal with...

If you use three conditional formats (for 1, 2, and 3), you can add in a custom number format for the other 2 conditions:

[Red][=4]General;[Blue][=5]General;General


I know this doesn't solve your issue with 7 conditions, but for folks who have just 4 or 5 conditions, this will get them some formating without using VBA.

 

[DeLaMartre]

Thanks for the Non-VBA solution, euskadi! I have filed this for future reference. This is great stuff!

Bob

 

[Guest_imported]

This whole list of postings has been REALLY useful for me but I have a question: I have only 5 conditional formats that I need, but they are letters and not numbers, so I cannot use the custom number format suggested by euskadi.

Here's the code I used, modified from above:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValue As Variant
vValue = Target.Value
On Error Resume Next
If vValue = "T" Then
Target.Interior.Color = RGB(255, 100, 0)
End If
If vValue = "A" Then
Target.Interior.Color = RGB(0, 255, 0)
End If
If vValue = "AE" Then
Target.Interior.Color = RGB(0, 255, 0)
End If
If vValue = "S" Then
Target.Interior.Color = RGB(255, 255, 0)
End If
If vValue = "FT" Then
Target.Interior.Color = RGB(255, 255, 0)
End If
If vValue = "P" Then
Target.Interior.Color = RGB(255, 255, 255)
End If
End Sub

This turns the cells the colors indicated. The only problem is that if I use fill right or fill down, it does not format all of those cells at all (it used to give me an error until I added the "On Error Resume Next" line.)

Suggestions???

Many thanks,
Dave

 

[acron]

Try this and see how you get on

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
             Case Is = "T"
                 oCell.Interior.Color = RGB(255, 100, 0)
             Case Is = "A", "AE"
                 oCell.Interior.Color = RGB(0, 255, 0)
             Case Is = "S"
                 oCell.Interior.Color = RGB(255, 255, 0)
             Case Is = "FT"
                 oCell.Interior.Color = RGB(255, 255, 0)
             Case Is = "P"
                 oCell.Interior.Color = RGB(255, 255, 255)
             Case Else
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

A.C.

 

[Guest_imported]

The code was very helpful. I have modified it to format a range of cells A1:E500 as shown below such that when the search argument is found, the cell is turned green.

However, the variables are fixed in the code (I used the numbers 1,2,3,4,5). Is there any way that these variables can be easily modified by being entered in a separate spreadsheet in the same workbook (call the new sheet 'data'), say, in cells A1:E1, and then the code can be rerun easily without actually having to 'reprogram' the code? Thanks. Victor Grech

Sub Activate()
Dim oCell As Range
For Each oCell In Range("A1:E500&quot
oCell.Interior.ColorIndex = xlNone
Next oCell
For Each oCell In Range("A1:E500&quot
Select Case oCell.Value
Case Is = 1
oCell.Interior.ColorIndex = 4
Case Is = 2
oCell.Interior.ColorIndex = 4
Case Is = 3
oCell.Interior.ColorIndex = 4
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 4
End Select
Next oCell
End Sub

 

[xlbo]

try this
Sub Activate()
Dim oCell As Range, v1, v2, v3, v4, v5
v1 = [A1].value
v2 = [B1].value
v3 = [C1].value
v4 = [D1].value
v5 = [E1].value
For Each oCell In Range("A2:E500&quot
oCell.Interior.ColorIndex = xlNone
Next oCell
For Each oCell In Range("A2:E500&quot
Select Case oCell.Value
Case Is = v1
oCell.Interior.ColorIndex = 1
Case Is = v2
oCell.Interior.ColorIndex = 2
Case Is = v3
oCell.Interior.ColorIndex = 3
Case Is = v4
oCell.Interior.ColorIndex = 4
Case Is = v5
oCell.Interior.ColorIndex = 5
End Select
Next oCell
End Sub

HTH
Geoff

 

[DaleWatson123321]

Geoff,

Yours is ALSO a GOOD example. THANKS ===> STAR

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bushido]

Thanks, it worked a charm.

 

[joem28a]

Hi folks,
I was wondering if someone could assist me with the following query. I wish to add more than 3 Conditional Formats in Excel (what a limitation). This is the scenario:

If cell Text = "ARL" then colour the cell Red
If cell Text = "P-ARL" then colour the cell Green
If cell Text = "S/L" then colour the cell Blue
If cell Text = "Maternity" then colour the cell Pink
and so on.

Could you please advise. Many thanks.

Regards,

Joe Maruca
Australia

 

[xlbo]

Have you not even bothered trying some of the many examples given? In particular, Acron's post of 13/April shows how you can deal with text

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[joem28a]

Geoff,

I honestly have; but they didn't seem to work ... I do not know why. I have copied and pasted the code, to no avail. I am obviously not doing something right; I really want to solve this. Thank you.

Regards,
Joe.

 

[xlbo]

This needs to go in the WORKSHEET module (goto VBE and double click on the worksheet in question in the projects window)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "ARL"
oCell.Interior.Colorindex = 3
Case Is = "P-ARL"
oCell.Interior.Colorindex = 4
Case Is = "S/L"
oCell.Interior.Colorindex = 5
Case Is = "Maternity"
oCell.Interior.Colorindex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[joem28a]

Hi there Geoff,

Fantastic !!!; just to go in a little deeper; what is the syntax for Font Type; Text Justification and other colours?.
How did you come across the "Colorindex number" as below?. My VBE Help is not worling for some reason. Many thanks.

oCell.Interior.Colorindex = 5

Regards,

Joe.

 

[joem28a]

Geoff,

I managed to figure out how to change the Font and Font Colour, etc. I have just applied the code in my Spreadsheet and have one little issue. What my spreadsheet does is when when you enter a value or text in one cell of a worksheet, I have a duplicate copy of what was entered on another worksheet within the same workbook. How I do this is simply, for example "='worksheet 1'A1". Unfortunately, the VB code doesn't seem to process it, even though the cell being populated matches the criteria in the code. Is there a way of manually/automatically executing this code?. I hope I have explained this properly. Many thanks.

Regrads,

Joe.