Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color - More Than Three Conditions
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color - More Than Three Conditions
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.....Download.html HTH, Elkar "Paperback Writer" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color - More Than Three Conditions
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.....Download.html Gord Dibben MS Excel MVP On Tue, 21 Nov 2006 09:29:01 -0800, Paperback Writer wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color - More Than Three Conditions
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. "Paperback Writer" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color - More Than Three Conditions
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 On Tue, 21 Nov 2006 11:40:02 -0800, OC wrote: 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. "Paperback Writer" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
change cell color to red & then green when value change in 2 cel | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Cell Change Color - Need Help | New Users to Excel |