Hi All,

Is there a way to add more then 3 conditional formatting criteria? I'd like
to add a forth or fifth.

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.

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.

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.