I would like to know how i can have 6 varieties of conditinal formats
specific ally for dates EG 30 days older than today , 60 days older than
Today ,90 days older than today etc

If you want conditional font colors, this may be useful:

http://www.mcgimpsey.com/excel/conditional6.html

In article ,
Eqa wrote:

I would like to know how i can have 6 varieties of conditinal formats
specific ally for dates EG 30 days older than today , 60 days older than
Today ,90 days older than today etc

Hi Eqa,

Also how do you know what number relates to what color where can I
find this out? What No. is orange?


For additional information on colours, see:

Chip Pearson
http://www.cpearson.com/excel/colors.htm#RGB


David McRitchie:
http://www.mvps.org/dmcritchie/excel/colors.htm


---
Regards,
Norman

Hi Eqa,

Norman,

I just don't seem to be able to get this to work. I am changing the column
selection from A to D and think I am following your instructions. However
does this new programme replce the previous ones you have sent me? I am
deleting them and then pasting this one is that correct? Can you walk me
thru
in plain Englishplesa.

Select the worksheet.
Right-click the sheet's tab
Copy and paste the following code:

'==================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell As Range

Set rng = Range("A1:A20") '<<==== CHANGE

rng.FormatConditions.Delete

If Not Intersect(rng, Target) Is Nothing Then
For Each rCell In Target.Cells
With rCell
If IsDate(.Value) Then
Select Case .Value
Case Is Date - 30: .Interior.ColorIndex = xlNone
Case Is Date - 60: .Interior.ColorIndex = 3
Case Is Date - 90: .Interior.ColorIndex = 4
Case Is Date - 120: .Interior.ColorIndex = 5
Case Is Date - 150: .Interior.ColorIndex = 6
Case Is Date - 180: .Interior.ColorIndex = 7
End Select
Else
.Interior.ColorIndex = xlNone
End If
End With
Next rCell
End If
End Sub
'<<==================

Now change the code line:

Set rng = Range("A1:A20") '<<==== CHANGE

to reflect your required range.

You can also change the ColorIndex values using the table produced with code
I suggested in an earlier response.

Then:

Alt IM (to insert a new module)

Now copy and paste the following code:

'==================
Public Sub ColorExistingData()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A1:A20") '<<==== CHANGE

rng.FormatConditions.Delete

For Each rCell In rng.Cells
With rCell
If IsDate(.Value) Then
Select Case .Value
Case Is Date - 30: .Interior.ColorIndex = xlNone
Case Is Date - 60: .Interior.ColorIndex = 3
Case Is Date - 90: .Interior.ColorIndex = 4
Case Is Date - 120: .Interior.ColorIndex = 5
Case Is Date - 150: .Interior.ColorIndex = 6
Case Is Date - 180: .Interior.ColorIndex = 7
End Select
Else
.Interior.ColorIndex = xlNone
End If
End With
Next rCell

End Sub
'<<==================

As with the Worksheet_Change code above, change the line:

Set rng = Range("A1:A20") '<<==== CHANGE

to reflect your required range and make any ColorIndex changes.

With your cursor placed in the code:

F5 (to run this macro)

Alt-F11 (to return to your worksheet)

Test that everything works as you would want it to.

If you are still experiencing problems, you may send me a copy of your
workbook.

norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).

---
Regards,
Norman