can anyone help with this

O

olive

New Member
Joined
Oct 9, 2002
Messages
24
HI hope this explains itself better than I did in the past.
Book1.xls
ABCDEFGHI
111-October-0228-Jun-02condition:dateinb1lessthan5monthsoftodaysdate=green
21-May-02condition:dateinb1=5to6monthspastoftodaysdate=yellow
322-Mar-02condition:dateinb1morethanor=6monthsoftodaysdate=red
4condition:ifnodatecellstayswhite
5
Sheet1
 
Sort by date Sort by votes
OK One last try!! :)
Here is a replica of a portion of the sheet (same amount of columns that are dealing with the dates but not rows have 179rows on original had to condense to fit). The top tells the time I need it to expire. Need it to show red if one day past the expiration (ie 1yr=365 turn red on 366). turn yellow for the 60 days prior to expiration (exception is the 1/4ly column 15 days). And green from today until time to turn yellow. But also need the cell to stay clear if no date is entered. THANK YOU for your patience!!
Book2
ABCDEFGHIJK
113-October-02
2EXP1YREXP6MONTHSEXPatbeginningofevery1/4EXPifOlderthantoday()dateEXP1YREXP1YREXP10YREXP5YRSEXP5YRSEXP3YREXP1YR
36-May-0217-Sep-0217-Jan-026-Jun-0322-May-029-Jun-9426-Apr-021-Sep-9629-Nov-002-Sep-01
430-Dec-0117-Sep-0217-Apr-021-Dec-0222-Sep-011-Nov-0129-Jun-9819-Sep-0129-Aug-0024-Apr-005-Jun-02
57-Oct-027-Aug-029-Jul-0210-Jan-019-Jul-022-Jul-026-Sep-0011-Jul-00
610-May-0213-May-0218-Jul-028-Jul-0222-May-0219-Dec-0111-Aug-0022-May-0219-Jan-9617-Nov-965-Jun-02
726-Apr-012-May-0315-Sep-0119-Dec-0128-Feb-0026-Apr-0213-Aug-0124-Mar-92
828-Aug-9917-Jan-0218-Oct-0226-Mar-0322-May-0218-Dec-0123-Aug-006-Apr-0128-Feb-0128-Feb-019-Jul-02
9
Sheet1

This message was edited by olive on 2002-10-13 03:01
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Book1
ABCDEFGHIJK
113-Oct-02
23651823653653650182518251095365
3EXP 1YREXP 6MONTHSEXP at beginning of every 1/4EXP if Older than today() dateEXP 1YREXP 1YREXP 10YREXP 5YRSEXP 5YRSEXP 3YREXP 1YR
406-May-0217-Sep-0217-Jan-0206-Jun-0322-May-029-Jun-9426-Apr-021-Sep-9629-Nov-002-Sep-01
530-Dec-0117-Sep-0217-Apr-0201-Dec-0222-Sep-011-Nov-0129-Jun-9819-Sep-0129-Aug-0024-Apr-005-Jun-02
607-Oct-027-Aug-029-Jul-0210-Jan-019-Jul-0202-Jul-026-Sep-0011-Jul-00
710-May-0213-May-0218-Jul-0208-Jul-0223-Aug-0019-Dec-0111-Aug-0022-May-0219-Jan-9617-Nov-965-Jun-02
826-Apr-0102-May-0315-Sep-0119-Dec-0128-Feb-0026-Apr-0213-Aug-0124-Mar-92
928-Aug-9917-Jan-0218-Oct-0226-Mar-0322-May-0218-Dec-0123-Aug-006-Apr-0128-Feb-0128-Feb-019-Jul-02
Sheet1


Is this the result you are looking for? i am working on column C....

Whoops i don't know whare all the colour went. Sorry
This message was edited by bolo on 2002-10-13 11:38
 
Upvote 0
Just I have tried with VBA it seems to be working. You can change the range to suite your no of rows.<Pre>Sub Macro()
Dim Cel As Range
Dim Add$
Set Cel = Range("A4:K9")

For Each C In Cel
Add = C.Address
For I = 65 To 75
If Mid(Add, 2, 1) = Chr(I) Then
If C.Value = Empty Then
' Do nothing
ElseIf Range(Chr(I) & "1") - C > 365 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range(Chr(I) & "1") - C > 305 And Range(Chr(I) & "1") - C< 366 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range(Chr(I) & "1") - C<= 305 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
End If
If I = 65 Then I = 68
If I = 70 Then I = 74
Next I

If Mid(Add, 2, 1) = "B" Then
If C.Value = Empty Then
' Do nothing
ElseIf Range("B1") - C > 180 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("B1") - C > 120 And Range("B1") - C< 181 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("B1") - C<= 120 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
ElseIf Mid(Add, 2, 1) = "C" Then
If C.Value = Empty Then
' Do nothing
ElseIf Range("C1") - C > 90 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("C1") - C > 15 And Range("C1") - C< 91 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("C1") - C<= 15 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
ElseIf Mid(Add, 2, 1) = "D" Then
If C.Value = Empty Then
' Do nothing
ElseIf Range("D1") - C > 1 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("D1") - C<= 1 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
ElseIf Mid(Add, 2, 1) = "G" Then
If C.Value = Empty Then
' Do nothing
ElseIf Range("G1") - C > 3650 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("G1") - C > 3590 And Range("G1") - C< 3651 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("G1") - C<= 3590 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
End If

For I = 72 To 73
If Mid(Add, 2, 1) = Chr(I) Then
If C.Value = Empty Then
' Do nothing
ElseIf Range(Chr(I) & "1") - C > 1825 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range(Chr(I) & "1") - C > 1765 And Range(Chr(I) & "1") - C< 1826 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range(Chr(I) & "1") - C<= 1765 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
End If
Next I
If Mid(Add, 2, 1) = "J" Then
If C.Value = Empty Then
' Do nothing
ElseIf Range("J1") - C > 1095 Then
C.Interior.ColorIndex = 3
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("J1") - C > 1035 And Range("J1") - C< 1096 Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
ElseIf Range("J1") - C<= 1035 Then
C.Interior.ColorIndex = 4
C.Interior.Pattern = xlSolid
C.Interior.PatternColorIndex = xlAutomatic
End If
End If
Next
End Sub</Pre>
Book1.xls
ABCDEFGHIJK
113-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-0213-Oct-02
23651809013653653650182518251095365
3EXP1YREXP6MONTHSEXPatbeginningofevery1/4EXPifOlderthantoday()dateEXP1YREXP1YREXP10YREXP5YRSEXP5YRSEXP3YREXP1YR
46-May-0217-Sep-0217-Jan-026-Jun-0322-May-029-Jun-9426-Apr-021-Sep-9629-Nov-002-Sep-01
530-Dec-0117-Sep-0217-Apr-021-Dec-0222-Sep-011-Nov-0129-Jun-9819-Sep-0129-Aug-0024-Apr-005-Jun-02
67-Oct-027-Aug-029-Jul-0210-Jan-019-Jul-022-Jul-026-Sep-0011-Jul-00
710-May-0213-May-0218-Jul-028-Jul-0222-May-0219-Dec-0111-Aug-0022-May-0219-Jan-9617-Nov-965-Jun-02
826-Apr-012-May-0315-Sep-0119-Dec-0128-Feb-0026-Apr-0213-Aug-0124-Mar-92
928-Aug-9917-Jan-0218-Oct-0226-Mar-0322-May-0218-Dec-0123-Aug-006-Apr-0128-Feb-0128-Feb-019-Jul-02
Sheet1

You can copy this code into workbook open event.
This message was edited by gnaga on 2002-10-13 10:45
 
Upvote 0
surely D5 should be yellow as it is within sixty days of 13th oct.

You do not need VBA for this question. I have got all the columns looking like yours just using CF. am have problems with the quartly one becasue the quaterly value changes depending on date. ie when 1/1/3 is reached the value on which CF is based on is changed. i don't think using a value of 90 will give the correct answers.
 
Upvote 0
Bolo & Gnaga HI and thank you !!
For some reason I can't figure out how to see the CF's that Bolo used but would rather use CF than VBA. Am not familiar with VBA at all. As I stated earlier I am excel illiterate! (except for basics, of course) :)
 
Upvote 0
i can't colo html utility to work.
Olive if you private message me your email i will send you the workbook.

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
252
amolvijay
A
M
  • Question
COUNTIFS formula for number of dates in a quarter??
Replies
4
Views
190
Alex Blakenburg
A
T
  • Solved
Conditional Format - Column Date Range & Another Variable
Replies
3
Views
86
Joe4
Joe4
E
  • Question
EXCEL QUESTION SUMIF WITHIN A ROW
Replies
1
Views
100
excelhelpmepls1
E
M
  • Solved
How to count certain month from a column
Replies
3
Views
109
Joe4
Joe4

Forum statistics

Threads
1,214,948
Messages
6,122,420
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top