How can I set more than three (3) conditions in the conditional formatting in
Excel?

Hi Giovanni

you have a couple of options:

1) Bob Phillips has an add-in available for download from his site
http://www.xldynamic.com/source/xld.....Download.html

2) John McGimpsey has notes on his site on how to do up to 6 without VBA

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

3) Use VBA - here's an example:

the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and
choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that
you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -
assuming you want the conditional formatting to work on cell B6

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Not Intersect(Target, Range("B6")) Is Nothing Then

With Target

Select Case .Value

Case 1: Range("B6").Font.ColorIndex = 4

Case 2: Range("B6").Font.ColorIndex = 3

Case 3: Range("B6").Font.ColorIndex = 0

Case 4: Range("B6").Font.ColorIndex = 6

Case 5: Range("B6").Font.ColorIndex = 13

Case 6: Range("B6").Font.ColorIndex = 46

Case 7: Range("B6").Font.ColorIndex = 11

Case 8: Range("B6").Font.ColorIndex = 7

Case 9: Range("B6").Font.ColorIndex = 55

End Select

End With

End If

ws_exit:

Application.EnableEvents = True

End Sub

--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.

if you'ld like additonal help with your criteria & formatting statements,
please feel free to post back with more details.

Cheers
JulieD

Giovanni G. Italy" <Giovanni G. wrote in
message ...
How can I set more than three (3) conditions in the conditional formatting
in
Excel?

"?B?R2lvdmFubmkgRy4gSXRhbHk=?=" <Giovanni G.
allegedly wrote in
:

How can I set more than three (3) conditions in the conditional
formatting in Excel?

This is one of the things I am using at present. I just need to figure out
how to set and use more than one range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
If Intersect(Target, Range("H3:HZ36")) Is Nothing Then Exit Sub
For Each cell In Target
Number = cell.Value
Select Case Number
Case "M", "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "L", "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "G", "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "T", "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next
End Sub

Like this???

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
Dim myRng As Range
Dim myIntersect As Range
Dim cell As Range

Set myRng = Me.Range("H3:HZ36,B1:c9,A1:a99")

On Error Resume Next
Set myIntersect = Intersect(Target, myRng)
On Error GoTo 0

If myIntersect Is Nothing Then Exit Sub

For Each cell In myIntersect
Number = cell.Value
Select Case LCase(Number)
Case "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next cell

End Sub

I changed your "select case" statement so that I only had to look at lower case
"numbers"???




David Flick wrote:

"?B?R2lvdmFubmkgRy4gSXRhbHk=?=" <Giovanni G.
allegedly wrote in
:

How can I set more than three (3) conditions in the conditional
formatting in Excel?

This is one of the things I am using at present. I just need to figure out
how to set and use more than one range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
If Intersect(Target, Range("H3:HZ36")) Is Nothing Then Exit Sub
For Each cell In Target
Number = cell.Value
Select Case Number
Case "M", "m"
cell.Interior.ColorIndex = 45
cell.Font.ColorIndex = 45
Case "L", "l"
cell.Interior.ColorIndex = 32
cell.Font.ColorIndex = 32
Case "G", "g"
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 15
Case "T", "t"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 4
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = 1
End Select
Next
End Sub

--

Dave Peterson

Dave Peterson allegedly wrote in
:

Like this???
[snip]
I changed your "select case" statement so that I only had to look at
lower case "numbers"???

Dave,

This seems to be just a bit faster than the routine I was using when the
worksheet gets to be a few MB in size. Since I am a bit (very) weak on VBA
I wonder if you might set me straight on another question?

What I would like to do is evaluate multiple conditions in different
ranges.
i.e.
1) Column B has a numeric value I would like to color code based on
content. Values are 1 through 6.

2) Columns C and D has four digit numeric values to evaluate for
conditional formatting.

3) Column H has an alpha-numeric value to evalute for conditional
formatting.

4) Columns M, N, and O have single character codes to evaluate for
conditional formatting.

I have tried copying the Sub down and changing what I thought were the
relevant components, but alas have not been able to fudge it along
sufficiently well to make it work as yet.

Dave Flick

One way to do it is check to see if the changed cell is in one of those ranges:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngB As Range
Dim myRngCD As Range
Dim myRngH As Range
Dim myRngMNO As Range

If Target.Cells.Count 1 Then Exit Sub

Set myRngB = Me.Range("B:B")
Set myRngCD = Me.Range("C:D")
Set myRngH = Me.Range("H")
Set myRngMNO = Me.Range("M:O")

If Not (Intersect(Target, myRngB) Is Nothing) Then
'do the work for column B
ElseIf Not (Intersect(Target, myRngCD) Is Nothing) Then
'do the work for C:D
ElseIf Not (Intersect(Target, myRngH) Is Nothing) Then
'do the work for H
ElseIf Not (Intersect(Target, myRngMNO) Is Nothing) Then
'do the work for M:O
End If

End Sub

==========
But since your ranges are complete columns, you could even make it look like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

Select Case Target.Column
Case Is = 2
'do the work for B
Case Is = 3, 4
'do the work for C:D
Case Is = 8
'column H
Case Is = 13, 14, 15
'columns M, N, 0
End Select

End Sub

The "do the work" stuff will look a lot like the previous posts.




David Flick wrote:

Dave Peterson allegedly wrote in
:

Like this???
[snip]
I changed your "select case" statement so that I only had to look at
lower case "numbers"???

Dave,

This seems to be just a bit faster than the routine I was using when the
worksheet gets to be a few MB in size. Since I am a bit (very) weak on VBA
I wonder if you might set me straight on another question?

What I would like to do is evaluate multiple conditions in different
ranges.
i.e.
1) Column B has a numeric value I would like to color code based on
content. Values are 1 through 6.

2) Columns C and D has four digit numeric values to evaluate for
conditional formatting.

3) Column H has an alpha-numeric value to evalute for conditional
formatting.

4) Columns M, N, and O have single character codes to evaluate for
conditional formatting.

I have tried copying the Sub down and changing what I thought were the
relevant components, but alas have not been able to fudge it along
sufficiently well to make it work as yet.

Dave Flick

--

Dave Peterson