increase limits for conditional formating for different dates
 

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

increase limits for conditional formating for different dates
 

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

increase limits for conditional formating for different dates
 

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

increase limits for conditional formating for different dates
 

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