Conditional formatting in Excel 2003(more than 3)Closed

Question

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.

TrowaD · Answer

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

Conditional formatting in Excel 2003(more than 3)

1 response

Similar discussions

Subscribe To Our Newsletter!