Conditional formatting in Excel 2003(more than 3)

[Closed]
Report
Posts
2
Registration date
Saturday April 6, 2013
Status
Member
Last seen
April 8, 2013
-
Posts
2
Registration date
Saturday April 6, 2013
Status
Member
Last seen
April 8, 2013
-
Hello all,

Excel 2003 novice here trying to teach myself.

I am trying to make some fields turn a certain colour depending on the number of weeks counted from a specific date.

In column D I have various different dates in each field.

In column E weeks are counted from the dates in column D.

I would like fields in column E to stay white for 0-25 weeks, turn orange for 26 to 33 weeks, yellow for 34 to 38 weeks and red for 39 weeks an above.

Would anybody be able to tell me if this is something I can do. Conditional formatting only allows me to use three colurs. I have had a look at Macros but I am struggling to get this to work. Any help would be greatly appreciated.

Thanks

Plangey.

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Plangey,

So column D is entered manually and column E is calculated using a formula.

This code should cover that:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub

If Target.Offset(0, 1).Value >= 0 And Target.Offset(0, 1).Value <= 25 Then
    Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Offset(0, 1).Value >= 26 And Target.Offset(0, 1).Value <= 33 Then
    Target.Offset(0, 1).Interior.ColorIndex = 45
ElseIf Target.Offset(0, 1).Value >= 34 And Target.Offset(0, 1).Value <= 38 Then
    Target.Offset(0, 1).Interior.ColorIndex = 6
ElseIf Target.Offset(0, 1).Value >= 39 Then
    Target.Offset(0, 1).Interior.ColorIndex = 3
End If
End Sub

To implement the code, right-click the sheets tab and select "View code", then paste the code in the big white field.

Hopefully the code makes some sense to you.

Best regards,
Trowa
Posts
2
Registration date
Saturday April 6, 2013
Status
Member
Last seen
April 8, 2013

Thanks for your info. I will give it a try when back at my computer. Plangey.