Changing a cell color based on date entry ...

Looking for solution to problem if anyone can help please ...

Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks before
today then cell color to be green.

I have used the sample code as detailed here ... http://tinyurl.com/3cex5
(kindly advised by Max in microsoft.public.excel) and need to tailor the
code to suit the different date ranges. Can anyone help out please as I'm
not sure how to build this is to the CASE statements.

Many thanks.

--
T. Denford.

Hello -

Do you need macro code for that or could you use conditional
formatting?

For conditional formatting do the following:
- Select the cell
- Format > Conditional Formatting...
- Condition 1 > "Cell Value is" "less than" "=TODAY()-14
- Format... Button > Pattern Tab > Select Red
- Add >> Button ... for Condition 2
- Condition 2 > "Cell Value is" "less than" "=TODAY()-7
- Format... Button > Pattern Tab > Select Yellow
- Add >> Button ... for Condition 3
- Condition 3 > "Cell Value is" "less than" "=TODAY()
- Format... Button > Pattern Tab > Select Green

You can use the following VB code to add the conditional formatting to
the currently selected cell:

Range("K16").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()-14"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()-7"
Selection.FormatConditions(2).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()"
Selection.FormatConditions(3).Interior.ColorIndex = 10
Selection.NumberFormat = "m/d/yyyy"

I think that would be the easiest way to do it if you want the color to
be updated upon entry. The other option would be to put code in the
Worksheet_Change() callback but that can get tricky too.

Joe

On 13 Apr 2005 03:56:10 -0700, Joe HM wrote:

> Hello -
>
> Do you need macro code for that or could you use conditional
> formatting?
>
> For conditional formatting do the following:
> - Select the cell
> - Format > Conditional Formatting...
> - Condition 1 > "Cell Value is" "less than" "=TODAY()-14
> - Format... Button > Pattern Tab > Select Red
> - Add >> Button ... for Condition 2
> - Condition 2 > "Cell Value is" "less than" "=TODAY()-7
> - Format... Button > Pattern Tab > Select Yellow
> - Add >> Button ... for Condition 3
> - Condition 3 > "Cell Value is" "less than" "=TODAY()
> - Format... Button > Pattern Tab > Select Green
>
> You can use the following VB code to add the conditional formatting to
> the currently selected cell:
>
> Range("K16").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
> _
> Formula1:="=TODAY()-14"
> Selection.FormatConditions(1).Interior.ColorIndex = 3
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
> _
> Formula1:="=TODAY()-7"
> Selection.FormatConditions(2).Interior.ColorIndex = 6
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
> _
> Formula1:="=TODAY()"
> Selection.FormatConditions(3).Interior.ColorIndex = 10
> Selection.NumberFormat = "m/d/yyyy"
>
> I think that would be the easiest way to do it if you want the color to
> be updated upon entry. The other option would be to put code in the
> Worksheet_Change() callback but that can get tricky too.
>
> Joe

Hi Joe,

Yes I need VB code to do this as I need to build in more than 3 conditional
formats.

--
T. Denford.

Hello T -

Here we go ... the following code applies the colors to the A1 Cell but
you can tweak it to work for a range or whatever you need ...

Private Sub Worksheet_Change(ByVal aTarget As Range)
If aTarget.Column = 1 And aTarget.Row = 1 Then
Set lTargetCell =
ThisWorkbook.Sheets("Sheet1").Cells(aTarget.Row, aTarget.Column)

If lTargetCell.Value < Date - 14 Then
lTargetCell.Interior.Color = vbRed
ElseIf lTargetCell.Value < Date - 7 Then
lTargetCell.Interior.Color = vbYellow
ElseIf lTargetCell.Value < Date Then
lTargetCell.Interior.Color = vbGreen
End If
End If
End Sub

Joe

This may be of some help. Add the following code to a standard code module

Option Explicit
Enum eColor
White = 16777215
Blue = 16737843
Red = 255
Green = 65280
Brown = 13209
yellow = 65535
Pink = 13408767
End Enum
Sub GetAndSetColors()
Dim index As Long
index = 1
Do Until Cells(index, "A") = ""
SetColor Cells(index, "A")
index = index + 1
Loop
End Sub
Private Sub SetColor(target As Range)
Dim clr As Long
Select Case True
Case target.Value >= Date
clr = eColor.White
Case target.Value > (Date - 7)
clr = eColor.Red
Case target.Value > (Date - 14)
clr = eColor.yellow
Case target.Value > (Date - 21)
clr = eColor.Green
Case Else
clr = eColor.Pink
End Select
target.Interior.Color = clr
End Sub

In the Worksheet's change event, add a call to the GetAndSetColors procedure:-

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
GetAndSetColors
End If
End Sub


Using ENUM allows you to create your own variable to which you can assign
colors - and this makes your code much mor ereadable. Also, using the SELECT
CASE again makes your code more manageable ...just add more CASE
staements...and its so much easier to read and debug than shed loads of
IF...ELSEIF statements

HTH


"T. Denford" wrote:

> Looking for solution to problem if anyone can help please ...
>
> Column A contains a list of date values (e.g. 11/04/2005).
>
> I'm looking for a way to change the cell color based on the following
> criteria ...
>
> If the date falls earlier than today but no earlier than a week before
> today then cell color to be red.
>
> If the date falls earlier than today but no earlier than two weeks before
> today then cell color to be yellow.
>
> If the date falls earlier than today but no earlier than three weeks before
> today then cell color to be green.
>
> I have used the sample code as detailed here ... http://tinyurl.com/3cex5
> (kindly advised by Max in microsoft.public.excel) and need to tailor the
> code to suit the different date ranges. Can anyone help out please as I'm
> not sure how to build this is to the CASE statements.
>
> Many thanks.
>
> --
> T. Denford.
>

Just to add -
After applying a constant to a cell colour format, Excel matches the colour
to the nearest that exists in the palette, then applies the colorindex
associated with the nearest matching colour. In other words, if the constant
as an RGB value does not exist in the palette the closest according to
Excel's (not very good) colour match algorithm is applied.

All the Enum constants given by Patrick do exist in Excel's Default palette,
and so will be matched exactly (assuming an uncustomized palette).

Some of these, but not the Pink & Brown, could be replaced by vb constants
that already exist, eg vbRed, vbYellow. vbBlue is not the same as Patrick's
Enum Blue, colorindex's 5 & 41 respectively.

I find it's somewhat slower to apply an RGB colour rather than a colorindex,
but unlikely to be noticed in such a routine.

Regards,
Peter T

"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> This may be of some help. Add the following code to a standard code module
>
> Option Explicit
> Enum eColor
> White = 16777215
> Blue = 16737843
> Red = 255
> Green = 65280
> Brown = 13209
> yellow = 65535
> Pink = 13408767
> End Enum
> Sub GetAndSetColors()
> Dim index As Long
> index = 1
> Do Until Cells(index, "A") = ""
> SetColor Cells(index, "A")
> index = index + 1
> Loop
> End Sub
> Private Sub SetColor(target As Range)
> Dim clr As Long
> Select Case True
> Case target.Value >= Date
> clr = eColor.White
> Case target.Value > (Date - 7)
> clr = eColor.Red
> Case target.Value > (Date - 14)
> clr = eColor.yellow
> Case target.Value > (Date - 21)
> clr = eColor.Green
> Case Else
> clr = eColor.Pink
> End Select
> target.Interior.Color = clr
> End Sub
>
> In the Worksheet's change event, add a call to the GetAndSetColors
procedure:-
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 1 Then
> GetAndSetColors
> End If
> End Sub
>
>
> Using ENUM allows you to create your own variable to which you can assign
> colors - and this makes your code much mor ereadable. Also, using the
SELECT
> CASE again makes your code more manageable ...just add more CASE
> staements...and its so much easier to read and debug than shed loads of
> IF...ELSEIF statements
>
> HTH
>
>
> "T. Denford" wrote:
>
> > Looking for solution to problem if anyone can help please ...
> >
> > Column A contains a list of date values (e.g. 11/04/2005).
> >
> > I'm looking for a way to change the cell color based on the following
> > criteria ...
> >
> > If the date falls earlier than today but no earlier than a week before
> > today then cell color to be red.
> >
> > If the date falls earlier than today but no earlier than two weeks
before
> > today then cell color to be yellow.
> >
> > If the date falls earlier than today but no earlier than three weeks
before
> > today then cell color to be green.
> >
> > I have used the sample code as detailed here ...
http://tinyurl.com/3cex5
> > (kindly advised by Max in microsoft.public.excel) and need to tailor the
> > code to suit the different date ranges. Can anyone help out please as
I'm
> > not sure how to build this is to the CASE statements.
> >
> > Many thanks.
> >
> > --
> > T. Denford.
> >

colorindex as opposed to color may also produce odd results if the user is
playing with their color palette. for instance my nice pale grey heading
became a bright yellow on a colleagues machine !

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Just to add -
> After applying a constant to a cell colour format, Excel matches the
> colour
> to the nearest that exists in the palette, then applies the colorindex
> associated with the nearest matching colour. In other words, if the
> constant
> as an RGB value does not exist in the palette the closest according to
> Excel's (not very good) colour match algorithm is applied.
>
> All the Enum constants given by Patrick do exist in Excel's Default
> palette,
> and so will be matched exactly (assuming an uncustomized palette).
>
> Some of these, but not the Pink & Brown, could be replaced by vb constants
> that already exist, eg vbRed, vbYellow. vbBlue is not the same as
> Patrick's
> Enum Blue, colorindex's 5 & 41 respectively.
>
> I find it's somewhat slower to apply an RGB colour rather than a
> colorindex,
> but unlikely to be noticed in such a routine.
>
> Regards,
> Peter T
>
> "Patrick Molloy" <[email protected]> wrote in
> message
> news:[email protected]...
>> This may be of some help. Add the following code to a standard code
>> module
>>
>> Option Explicit
>> Enum eColor
>> White = 16777215
>> Blue = 16737843
>> Red = 255
>> Green = 65280
>> Brown = 13209
>> yellow = 65535
>> Pink = 13408767
>> End Enum
>> Sub GetAndSetColors()
>> Dim index As Long
>> index = 1
>> Do Until Cells(index, "A") = ""
>> SetColor Cells(index, "A")
>> index = index + 1
>> Loop
>> End Sub
>> Private Sub SetColor(target As Range)
>> Dim clr As Long
>> Select Case True
>> Case target.Value >= Date
>> clr = eColor.White
>> Case target.Value > (Date - 7)
>> clr = eColor.Red
>> Case target.Value > (Date - 14)
>> clr = eColor.yellow
>> Case target.Value > (Date - 21)
>> clr = eColor.Green
>> Case Else
>> clr = eColor.Pink
>> End Select
>> target.Interior.Color = clr
>> End Sub
>>
>> In the Worksheet's change event, add a call to the GetAndSetColors
> procedure:-
>>
>> Option Explicit
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Column = 1 Then
>> GetAndSetColors
>> End If
>> End Sub
>>
>>
>> Using ENUM allows you to create your own variable to which you can assign
>> colors - and this makes your code much mor ereadable. Also, using the
> SELECT
>> CASE again makes your code more manageable ...just add more CASE
>> staements...and its so much easier to read and debug than shed loads of
>> IF...ELSEIF statements
>>
>> HTH
>>
>>
>> "T. Denford" wrote:
>>
>> > Looking for solution to problem if anyone can help please ...
>> >
>> > Column A contains a list of date values (e.g. 11/04/2005).
>> >
>> > I'm looking for a way to change the cell color based on the following
>> > criteria ...
>> >
>> > If the date falls earlier than today but no earlier than a week before
>> > today then cell color to be red.
>> >
>> > If the date falls earlier than today but no earlier than two weeks
> before
>> > today then cell color to be yellow.
>> >
>> > If the date falls earlier than today but no earlier than three weeks
> before
>> > today then cell color to be green.
>> >
>> > I have used the sample code as detailed here ...
> http://tinyurl.com/3cex5
>> > (kindly advised by Max in microsoft.public.excel) and need to tailor
>> > the
>> > code to suit the different date ranges. Can anyone help out please as
> I'm
>> > not sure how to build this is to the CASE statements.
>> >
>> > Many thanks.
>> >
>> > --
>> > T. Denford.
>> >
>
>

> colorindex as opposed to color may also produce odd results if the user is
> playing with their color palette. for instance my nice pale grey heading
> became a bright yellow on a colleagues machine !

I totally agree! I play with palettes a lot !!

Regards,
Peter T


> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > Just to add -
> > After applying a constant to a cell colour format, Excel matches the
> > colour
> > to the nearest that exists in the palette, then applies the colorindex
> > associated with the nearest matching colour. In other words, if the
> > constant
> > as an RGB value does not exist in the palette the closest according to
> > Excel's (not very good) colour match algorithm is applied.
> >
> > All the Enum constants given by Patrick do exist in Excel's Default
> > palette,
> > and so will be matched exactly (assuming an uncustomized palette).
> >
> > Some of these, but not the Pink & Brown, could be replaced by vb
constants
> > that already exist, eg vbRed, vbYellow. vbBlue is not the same as
> > Patrick's
> > Enum Blue, colorindex's 5 & 41 respectively.
> >
> > I find it's somewhat slower to apply an RGB colour rather than a
> > colorindex,
> > but unlikely to be noticed in such a routine.
> >
> > Regards,
> > Peter T
> >
> > "Patrick Molloy" <[email protected]> wrote in
> > message
> > news:[email protected]...
> >> This may be of some help. Add the following code to a standard code
> >> module
> >>
> >> Option Explicit
> >> Enum eColor
> >> White = 16777215
> >> Blue = 16737843
> >> Red = 255
> >> Green = 65280
> >> Brown = 13209
> >> yellow = 65535
> >> Pink = 13408767
> >> End Enum
> >> Sub GetAndSetColors()
> >> Dim index As Long
> >> index = 1
> >> Do Until Cells(index, "A") = ""
> >> SetColor Cells(index, "A")
> >> index = index + 1
> >> Loop
> >> End Sub
> >> Private Sub SetColor(target As Range)
> >> Dim clr As Long
> >> Select Case True
> >> Case target.Value >= Date
> >> clr = eColor.White
> >> Case target.Value > (Date - 7)
> >> clr = eColor.Red
> >> Case target.Value > (Date - 14)
> >> clr = eColor.yellow
> >> Case target.Value > (Date - 21)
> >> clr = eColor.Green
> >> Case Else
> >> clr = eColor.Pink
> >> End Select
> >> target.Interior.Color = clr
> >> End Sub
> >>
> >> In the Worksheet's change event, add a call to the GetAndSetColors
> > procedure:-
> >>
> >> Option Explicit
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Target.Column = 1 Then
> >> GetAndSetColors
> >> End If
> >> End Sub
> >>
> >>
> >> Using ENUM allows you to create your own variable to which you can
assign
> >> colors - and this makes your code much mor ereadable. Also, using the
> > SELECT
> >> CASE again makes your code more manageable ...just add more CASE
> >> staements...and its so much easier to read and debug than shed loads of
> >> IF...ELSEIF statements
> >>
> >> HTH
> >>
> >>
> >> "T. Denford" wrote:
> >>
> >> > Looking for solution to problem if anyone can help please ...
> >> >
> >> > Column A contains a list of date values (e.g. 11/04/2005).
> >> >
> >> > I'm looking for a way to change the cell color based on the following
> >> > criteria ...
> >> >
> >> > If the date falls earlier than today but no earlier than a week
before
> >> > today then cell color to be red.
> >> >
> >> > If the date falls earlier than today but no earlier than two weeks
> > before
> >> > today then cell color to be yellow.
> >> >
> >> > If the date falls earlier than today but no earlier than three weeks
> > before
> >> > today then cell color to be green.
> >> >
> >> > I have used the sample code as detailed here ...
> > http://tinyurl.com/3cex5
> >> > (kindly advised by Max in microsoft.public.excel) and need to tailor
> >> > the
> >> > code to suit the different date ranges. Can anyone help out please as
> > I'm
> >> > not sure how to build this is to the CASE statements.
> >> >
> >> > Many thanks.
> >> >
> >> > --
> >> > T. Denford.
> >> >
> >
> >
>
>

T.Denford:
I'm not quite sure if it may help you but I have done a macro with different conditions respect to the dates but the logic applies. The 38468 is the numeric value of the date April 26 2005. Here it is:
Sub Macro4()

Dim MyCell As Range

For Each MyCell In Selection
Selection.FormatConditions.Delete
If MyCell.Value = 38468 Then
MyCell.Interior.ColorIndex = 3
Else
If MyCell.Value > 38468 Then
If MyCell.Value < 38468 + 16 Then
MyCell.Interior.ColorIndex = 6
End If
End If
End If
Next
End Sub

Let me know about it. Bye
Mónica