Conditional formatting in Excel 2003(more than 3)

Closed
plangey Posts 2 Registration date Saturday April 6, 2013 Status Member Last seen April 8, 2013 - Apr 6, 2013 at 01:44 PM
plangey Posts 2 Registration date Saturday April 6, 2013 Status Member Last seen April 8, 2013 - Apr 8, 2013 at 04:05 PM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Apr 8, 2013 at 10:27 AM
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
0
plangey Posts 2 Registration date Saturday April 6, 2013 Status Member Last seen April 8, 2013
Apr 8, 2013 at 04:05 PM
Thanks for your info. I will give it a try when back at my computer. Plangey.
0