Prev: If not an exact match to left of comma, then ALERT
Next: Can a cell be turned on or off?
From: Karen on 8 Mar 2010 14:38
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
From: Bob Phillips on 8 Mar 2010 16:33
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.

--

HTH

Bob

"Karen" <Karen(a)discussions.microsoft.com> wrote in message
news:1474C980-B607-4178-A23E-6BA3C23177E8(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: If not an exact match to left of comma, then ALERT
Next: Can a cell be turned on or off?