Previous Topic Next Topic

Excel 2003

Closed
smokeyschrader Posts 1 Registration date Tuesday November 6, 2012 Status Member Last seen November 6, 2012 - Nov 6, 2012 at 04:55 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 13, 2012 at 09:49 AM
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

Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 8, 2012 at 09:48 AM
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
0
smokeyschrader
Nov 12, 2012 at 02:59 PM
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
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 13, 2012 at 09:49 AM
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses