Conditional formating

[Jay]

Is there a way to get around the 3 condition limit of conditional formatting in Excel 2000

 

[Bob Phillips]

There are 4 if you include the default colour (which doesn't have to be no
colour).

To get more you need VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a way to get around the 3 condition limit of conditional

formatting in Excel 2000

 

[Bob Phillips]

Jay,

Essentially you need VBA programming. Here is an example

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Count = 1 Then
If .Column = 1 Then
Select Case .Value
Case Is = 1
.Interior.ColorIndex = 3 'red
Case Is = 2
.Interior.ColorIndex = 38 'pink
Case Is = 3
.Interior.ColorIndex = 4 'green
Case Is = 4
.Interior.ColorIndex = 6 'yellow
Case Is = 5
.Interior.ColorIndex = 8 'majenta
Case Is = 6
.Interior.ColorIndex = 5 'blue
Case Is = 7
.Interior.ColorIndex = 15 'grey
Case Is = 8
.Interior.ColorIndex = 38 'rose
Case Is = 9
.Interior.ColorIndex = 1 'teal
Case Else 'none of the above numbers
Exit Sub
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub

This is worksheet coide and goes in the worksheet coide module (right-click
on tghe sheet name tab, select the View Code menu option, \and paste the
code in).


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there an easy way to use VBA or do I need to know code language. I am

making color coded labels and colors are based on manually entered numbers
0-9. Any ideas?

 

[Jay Hall]

Thanks Bob

That will get me started and I think I can handle it from
here.

Jay

 

[David McRitchie]

You can see your color choices on
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[Holly]

Dave
I copied your script on conditional formating. Have to admit I've NEVER done this and am totally guessing on how to do this after reading yours and Bob's link references. I made changes to the colors and the triggers, but don't know how to tell the script to look at column Y and change colors in columns BCD of that same row.

How do I know if the script is "saved" how do I know if it is "running" and did I guess right at how to direct the script to the right columns? (I also tried typing in -21 as the target range instead of B, didn't seem to work.

I pasted it into the worksheet code by right clicking on the tab

Private Sub Worksheet_Change(ByVal Target As Range
'David McRitchie, 2000-08-08 rev. 2000-08-1
' http://www.mvps.org/dmcritchie/excel/event.ht

Dim vLetter As Strin
Dim vColor As Intege
Dim cRange As Rang
Dim cell As Rang
'***************** check range ***
Set cRange = Intersect(Range("Y:Y"), Range(Target("B").Address)
If cRange Is Nothing Then Exit Su
'*********************************
For Each cell In Targe
vLetter = UCase(Left(cell.Value & " ", 1)
'see colors.htm and event.htm in same directory a
' http://www.mvps.org/dmcritchie/excel/excel.ht
vColor = 0 'default is no colo
'-- 1 53 52 51 49 11 55 56 9 46 12 10 14 5 47 16 3 45 43 50 42 41 13 48 7 44 6 4 8 33 54 15 38 40 36 35 34 37 39 2 (see colors page) 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 '-

Select Case vLette
Case "A
vColor = 3
Case "B
vColor = 3
Case "C
vColor = 4
Case "D
vColor = 3
Case "K
vColor = 1

End Selec
Application.EnableEvents = False 'should be part of Change macr
cell.Interior.ColorIndex = vColo
Application.EnableEvents = True 'should be part of Change macr
Next cel
'Target.Offset(0, 1).Interior.colorindex = vColo
' use Text instead of Interior if you prefe
End Su

THANKS!!

Holly

 

[Dave Peterson]

I'm not David, well, I'm not that David, but this worked ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
'http://www.mvps.org/dmcritchie/excel/event.htm

Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
Set cRange = Intersect(Me.Range("y:y"), Target)

If cRange Is Nothing Then Exit Sub

For Each cell In cRange.Cells
vLetter = UCase(Left(cell.Value & " ", 1))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color

Select Case vLetter
Case "A"
vColor = 35
Case "B"
vColor = 36
Case "C"
vColor = 40
Case "D"
vColor = 34
Case "K"
vColor = 15
End Select
' Application.EnableEvents = False 'should be part of Change macro
Me.Cells(cell.Row, "B").Resize(1, 3).Interior.ColorIndex = vColor
' Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub

You don't actually need the application.enableevents stuff when you're just
changing colors. If you decide to changed values, uncomment them.