Excel 2003

[Closed]
Report
Posts
1
Registration date
Tuesday November 6, 2012
Status
Member
Last seen
November 6, 2012
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,

Can you help me, I am trying to write a conditonal formula in Excel 2003 that goes something like:

If current date is 6mths from date of entry - Colour 1
If current date = date required to contact - Colour 2
If current dte = less than date required to contact - colour 3
If current date = more than date required to contact - Colour 4


cheers and thanks

2 replies

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

Check out Aquarelle's answer within this link:
https://ccm.net/forum/affich-21716-conditional-formatting-more-than-3-in-excel

Let me know if you can't figure it out or get stuck somewhere.

Best regards,
Trowa

Hi Trowa


Thank you so much for getting back to me, sorry about the delay in replying, I was away for a few days!


That is definitely helpful, but where I am coming unstuck is trying to work out for example, how to specify if the current date is 6 months form the date of entry then change the colour? Any ideas? Any help is most appreciated.


cheers
Ann


If current date is 6mths from date of entry Colour 1
If current date = date required to contact - Colour 2
If current date = less than date required to contact - Colour 3
If current date = more than date required to contact - Colour 4
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Ann,

See if the following code yields the desired result:
Private Sub Worksheet_Change(ByVal Target As Range)
    
For Each cell In Range("R7:R1000")

'If current date = date required to contact - Colour 2
    If cell.Value = Date Then
        cell.Interior.ColorIndex = 3 'red
    End If
'If current date = more than date required to contact - Colour 4
    If Date - cell.Value > 0 Then
        cell.Interior.ColorIndex = 45 'orange
    End If
'If current date = less than date required to contact - Colour 3
    If Date - cell.Value < 0 Then
        cell.Interior.ColorIndex = 10 'green
    End If
'If current date is 6mths (180 days) from date of entry Colour 1
    If Date - cell.Value > 180 Or Date - cell.Value < -180 Then
        cell.Interior.ColorIndex = 5 'blue
    End If
'Reset color if cell is empty
    If cell.Value = vbNullString Then cell.Interior.ColorIndex = xlNone
    
Next cell

End Sub

To use the colors you like, record a macro to find out it's colorindex number.

Best regards,
Trowa