Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
I would like to use the conditional formatting to color code the cell
depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
You cannot add more than 3 conditions to Conditional Formatting, but you can
do what you want using VBA. Which cells do you want to have this functionality and is what you showed us exactly what is in the cells (that is, is the number, dash, description in the cell or just the number)? -- Rick (MVP - Excel) "juliejg1" wrote in message ... I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
There are a couple of options here...
http://www.xldynamic.com/source/xld.....Download.html http://www.mcgimpsey.com/excel/conditional6.html -- HTH... Jim Thomlinson "juliejg1" wrote: I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green Case 5: .Interior.ColorIndex = 46 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "juliejg1" wrote in message ... I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
I assume you are not running Excel 2007 which allows more than 3 conditions.
In earlier versions you will need to employ VBA Here is some event code to place in the sheet module.............right-click on sheet tab and "View Code". Paste the code into that module. Edit to suit. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A10") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1-High", "2-Med", "3-Low", "4-On Hold", "5-Cancelled") nums = Array(8, 9, 6, 3, 7) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord Dibben MS Excel MVP On Thu, 9 Jul 2009 07:11:01 -0700, juliejg1 wrote: I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
The cells in A2:A452 contain this exactly:
1-High 2-Med 3-Low 4-On Hold 5-Closed 1-High Needs to be colored green 2-Med Needs to be colored yellow 3-Low Needs to be colored red 4-On Hold Needs to be colored orange 5-Closed Needs to be colored black "Rick Rothstein" wrote: You cannot add more than 3 conditions to Conditional Formatting, but you can do what you want using VBA. Which cells do you want to have this functionality and is what you showed us exactly what is in the cells (that is, is the number, dash, description in the cell or just the number)? -- Rick (MVP - Excel) "juliejg1" wrote in message ... I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
Perfeft!!! Thanks!
"Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green Case 5: .Interior.ColorIndex = 46 'orange End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "juliejg1" wrote in message ... I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 criteria in Conditional Formatting
I see you have your answer from one of the other respondents, so I'll leave
it at that. -- Rick (MVP - Excel) "juliejg1" wrote in message ... The cells in A2:A452 contain this exactly: 1-High 2-Med 3-Low 4-On Hold 5-Closed 1-High Needs to be colored green 2-Med Needs to be colored yellow 3-Low Needs to be colored red 4-On Hold Needs to be colored orange 5-Closed Needs to be colored black "Rick Rothstein" wrote: You cannot add more than 3 conditions to Conditional Formatting, but you can do what you want using VBA. Which cells do you want to have this functionality and is what you showed us exactly what is in the cells (that is, is the number, dash, description in the cell or just the number)? -- Rick (MVP - Excel) "juliejg1" wrote in message ... I would like to use the conditional formatting to color code the cell depending on whether the cell says "1-High", "2-Med", "3-Low", "4-On Hold", "5-Canceled". Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting 2 criteria in one row | Excel Discussion (Misc queries) | |||
Conditional formatting for many criteria | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA | Excel Discussion (Misc queries) | |||
Conditional Formatting with more than 3 Criteria | Excel Discussion (Misc queries) | |||
Can I set more than three criteria for conditional formatting? | Excel Worksheet Functions |