Conditional Formatting

  • Thread starter Thread starter Jean Luc
  • Start date Start date
J

Jean Luc

Hi everybody

I use Excel 2002 sp2
Is there a means to have more than 3 conditions for
confitional formatting ? the dialog box only features 3.

many thanks and best regards

Jean Luc
 
J.E. McGimpsey shows a way to get up to 6 different formats using conditional
formatting and a custom number format.

You can't add more conditions, but you may be able to use a worksheet event that
formats the cell the way you want.
 
Dave
Thank you for your reply, but :
What is a worksheet event ? (Not found in help)
Best regards
Jean Luc
 
Worksheet events are macros that are run when something changes in the
worksheet. You don't run them via Tools|macro|Macros... or by clicking a
button.

They sit in the background waiting for you to do something the developer
expected--say put a numeric value in A1. Then when you do, it notices the
change and does something to help.

Look in VBA's help--not excel's help, and you'll find plenty of stuff.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

and for notes about events, you can visit David's page:
http://www.mvps.org/dmcritchie/excel/event.htm

or Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

A typical example is to provide more formatting than Format|conditional
formatting will allow.

Try this against a test worksheet.
Right click on the worksheet tab
select view code
paste this in the code window (usually the right hand side)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, me.Range("A:A")) Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = 1: .Interior.ColorIndex = 5
Case Is = 2: .Interior.ColorIndex = 6
Case Is = 3: .Interior.ColorIndex = 7
Case Is = 4: .Interior.ColorIndex = 8
Case Is = 5: .Interior.ColorIndex = 9
Case Is = 6: .Interior.ColorIndex = 10
Case Else
.Interior.ColorIndex = xlNone
End Select
End With

End Sub

Now get back to excel and type some numbers (1-6) in column A of that worksheet.

(If you've enabled macros, you'll see some changes the fill color.)
 
Back
Top