Possible Conditional Formatting

[Karen]

Using Excel 2003
I have 9 columns
A: Last Name
B: First Name
C: Initial Training
D: 6 mo. Training
E: 1 Year Training
F: 2 Year Training
G: 3 Year Training
H: 4 Year Training
I: 5 Year Training

In the intial training column (C) I am going to record a month and year
(Mar-10).
I want the "6 mo. Training" column (D) to turn (for example) red fill and
white font on Sep-10. Then in the "1 Year Training" column (E) to turn (for
example) green fill with white font on Mar-11 and so on. How can this be
done. Do I have to put a formula in the cells and then apply conditional
formatting? The problem is, I can only use up to 3 conditions. Note: Even if
I enter the initial training on the day of 3/31/10, I still want the six
month to be Sep-10 and so on. How can I accomplish this?
ANY help would be greatly appreciated, Karen

 

[Bob Phillips]

You can do it with event code, where you trap a change on a cell, something
like this


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "D:I" '<=== change to suit
Const CI_RED As Long = 3
Const CI_GREEN As Long = 4

On Error GoTo ws_exit:
Application.EnableEvents = False

'here we test if the cell being changed is one we carea about
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

'it is so we test which
With Target
Select Case .Column

Case 4: 'column D Test for 6 months
If CLng(Format(.Value2, "yyyymm")) = _
CLng(Format(.Offset(0, -1).Value2, "yyyymm")) + 6
Then

.Interior.ColorIndex = CI_RED
.Font.ColorIndex = 1
End If

Case 5: 'column E Test for 12 months
If CLng(Format(.Value2, "yyyymm")) = _
CLng(Format(.Offset(0, -2).Value2, "yyyymm")) + 100
Then

.Interior.ColorIndex = CI_GREEN
.Font.ColorIndex = 1
End If

'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.