Conditional Formatting more than 3?

[Ken]

Excel 2000 ... Conditional formatting ... an abosolutely
wonderful feature, but why limited to 3 conditions?

Is there a way around this?

I have a Tab Sheet containing 16 monthly Calendars (dates
set by formula) ... These Calendars contain a target date
based on date entered into another cell ... Within the
Calendar I use Conditional Formatting to high-lite:

All dates ... (Format White) ... (Condition 1)
Target date ... (Format Yellow) ... (Condition 2)

Above said ... I have various +/- tollerance windows
around each target date (+/- window is ... 5, 14, 27, & 55
days) ... I have a Conditional Formatting formula (from
this board) that high-lites this tollerance window
perfectly (Condition 3).

Issue is ... All tollerance windows high-lite same
color ... This is certainly Plan B ... However, I am
dealing with a pre-approved Company Form that presently
shows these windows as separate colors.

Conditional Formatting is such a fine feature that I don't
think you could ever have enough Conditions ... However,
being limited to 3 is a little weak ...

So, I once again ask the many respected Magicians on this
board that are intimate with Excel ... What do you have?

Thanks ... Kha

 

[Bob Phillips]

Ken,

3 is the limit. You can have a default, that is one that no condition
matches which is effectively 4, but that's you lot.

If you wan t more, you need worksheet event code.

--

HTH

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

 

[Ken]

Good morning "Bob" & "Others"

OK ... can you tell me more about "Work Sheet Event Code"?

I have a Field that list the dates ... ie:

Open Window (Cell D2) value = 01/15/04
Target Date (Cell D3) value = 01/20/04
Close Window(Cell D4) value = 01/25/04

Above is sample of a Target date with a +/- 5 day window.
My other windows are presently +/- 14, 27 & 55 days.
Conditional formatting within my monthly Calendars looks
at dates above & high-lites. This works fine except all
tollerance windows high-lite same color & I would like to
have them high-lite pre-approved colors. (Note: at this
point I have used my 3 Conditional Formatting Conditions).

Thanks for your input & assitance ... Kha

 

[Dave Peterson]

Chip Pearson has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

I'm not quite sure what's going on, but here's a simple example:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("d2,d3,d4")) Is Nothing Then Exit Sub

With Target
If IsDate(.Value) Then
Select Case Abs(.Value - Date)
Case Is < 5: .Interior.ColorIndex = 12
Case Is < 14: .Interior.ColorIndex = 13
Case Is < 18: .Interior.ColorIndex = 17
Case Is < 55: .Interior.ColorIndex = 22
Case Else: .Interior.ColorIndex = xlNone
End Select
End If
End With
End Sub

Right click on the worksheet tab that should have this behavior. Select view
code and paste this in.

Now back to excel and type a date in D2 (or d3, or d4).

The worksheet_change event is lurking in the background waiting for the user to
change the data. There's another event, worksheet_calculcate, that would apply
if the values changed as a result of a formula.

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