# 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

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,

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
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
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