Conditional Formatting: Adding more then 3
Hi All,
Is there a way to add more then 3 conditional formatting criteria? I'd like to add a forth or fifth. |
Conditional Formatting: Adding more then 3
Hi,
One solution is use the worrksheet change event and select case. Right click the sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A5")) Is Nothing Then Select Case Target.Value Case Is < 10 icolour = 3 Case Is = 10 icolour = 4 Case Is < 20 icolour = 5 Case Is < 30 icolour = 6 Case Else icolour = xlNone End Select Target.Interior.ColorIndex = icolour End If End Sub Mike "davednconfused" wrote: Hi All, Is there a way to add more then 3 conditional formatting criteria? I'd like to add a forth or fifth. |
Conditional Formatting: Adding more then 3
If the data are numbers see John McGimpsey's site for a method to get up to 6
conditions. http://www.mcgimpsey.com/excel/conditional6.html Or Bob Phillips' CFPlus add-in will do up to 30 http://www.xldynamic.com/source/xld.....Download.html Or upgrade to Excel 2007 Or use sheet event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("C4:IR30") 'edit to suit If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") 'edit to suit nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) 'edit to suit 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 Wed, 16 Jul 2008 11:43:00 -0700, davednconfused wrote: Hi All, Is there a way to add more then 3 conditional formatting criteria? I'd like to add a forth or fifth. |
Conditional Formatting: Adding more then 3
Here are a couple more examples:
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm http://www.tek-tips.com/viewthread.cfm?qid=223068 Regards, Ryan--- -- RyGuy "Mike H" wrote: Hi, One solution is use the worrksheet change event and select case. Right click the sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A5")) Is Nothing Then Select Case Target.Value Case Is < 10 icolour = 3 Case Is = 10 icolour = 4 Case Is < 20 icolour = 5 Case Is < 30 icolour = 6 Case Else icolour = xlNone End Select Target.Interior.ColorIndex = icolour End If End Sub Mike "davednconfused" wrote: Hi All, Is there a way to add more then 3 conditional formatting criteria? I'd like to add a forth or fifth. |
All times are GMT +1. The time now is 04:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com