Change Cell Color - More Than Three Conditions

[Guest]

I have an issue where I need to change the color of a cell based on a
particular value. Normally, I'd use conditional formatting, but this time I
have more than three conditions. In fact, I have five.

How should I do this?

 

[Guest]

Do you have more than three "conditions"? Or is it that you need more than
three formats?

Conditional Formatting is not limited to 3 conditions. You can actually
have several if you use the AND and OR functions. The number of formats,
however, is limited to 3 (4 if you count the default format).

If you provide some more detail, we might be able to suggest a solution.

Also, here are a couple links that may be of interest:

http://www.mcgimpsey.com/excel/conditional6.html

http://www.xldynamic.com/source/xld.CFPlus.Download.html

HTH,
Elkar


:

 

[Gord Dibben]

If the data consists of numerics only, JE McGimpsey shows how to change Font
color for up to 6 conditions.......note FONT color only.

http://www.mcgimpsey.com/excel/conditional6.html

Otherwise VBA would be required.

Bob Phillips has an add-in that will allow up to 30 conditions.

http://www.xldynamic.com/source/xld.CFPlus.Download.html


Gord Dibben MS Excel MVP

 

[Guest]

Here's a simple vba code you can use.
Sub COLOR()
'
' COLOR Macro

For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 39
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 45
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 8
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 4
End If
Next c

End Sub

I'm sorry I can't find my table to tell you what all the colors are. If I
find it i'll send it to you.

 

[Gord Dibben]

Slightly shorter version.

Sub colorit()
Dim Num As Long
Dim rng As Range
For Each rng In Selection
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
rng.Interior.ColorIndex = Num
Next rng
End Sub

If text values use Case is = "text": Num = 6

To get the colorindex numbers run this macro on a new worksheet.

Sub ListColors()
Dim a As Long
For a = 1 To 56
Cells(a, 1).Interior.ColorIndex = a
Cells(a, 2).Value = a
Next a
End Sub


Gord Dibben MS Excel MVP