Formula in excel 1

[Sopman]

Can someone help with writing a formula for this scenario. There is data in cells A1, B1, C1. In D1 is Mar 99. In six months I would like cells A1, B1, and C1 to be shaded in, to let me know that prev. maint is needed.

Thanks

 

[Databaseguy]

Private Sub Workbook_Open()
Dim MyDate
Range("D1&quot.SelectPrivate Sub Workbook_Open()
Dim MyDate
Range("D1&quot.Select
MyDate = Selection
If Now() - MyDate > 180 Then
Range("A1:C1&quot.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub

MyDate = Selection
If Now() - MyDate > 180 Then
Range("A1:C1&quot.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Sopman]

I tried your formula. I went to Macro - Visual Basic Editor and pasted it in. The line:
Range("D1&quot.SelectPrivate Sub Workbook_Open()
turned red and I get a compiler error. Sub is highlighted.

 

[JVFriederick]

As an alternative to DBG, I would suggest adding another column E, which is the projected PM date (column D now being the date of last PM activity).

After that it would be a simple matter to Conditional Format cells A, B, and C using a formula :
=$E$1-$D$1>180

 

[Sopman]

JVFriederick,
I understand what you are doing, but not sure where to insert your line!!!

 

[SHardy]

I think you'll find that DBG just mis-pasted his code. Should've read:

Private Sub Workbook_Open()
Dim MyDate
Range("D1&quot.Select
MyDate = Selection
If Now() - MyDate > 180 Then
Range("A1:C1&quot.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub


Although I've gotta say, I think I'd probably go for JVF's solution.

Something that would have to be amended in both, however, is the number of days in the condition. You said "in six months". Presumably from now? In which case the PM is required once a year? If this is a case you would need to replace 180 with 365.

 

[SHardy]

Or even once every three years. You said March99. Therefore use the appropriate number of days.

 

[SHardy]

Sopman:

Highlight the cells that need shading upon meeting the condition. From the menus choose "Format", "Conditional Formatting...". Change the dropdown (that currently says "Cell value is&quot to "Formula is". Then enter the formula into the box provided.

 

[SHardy]

Oh, and don't forget to choose the required format by clicking on the "Format..." button in the dialog.

 

[JVFriederick]

Your question to me was answered by SHardy (thanks BTW). Highlight the cells in columns A,B,C and then follow his advice. I should also metion the formula should be
=$E1-$D1>180
to allow copying to other rows.

This should take care of it. If not, write back !!

 

[Sopman]

JVF’s solution I was able to do, but what I meant about the PM was we are trying to be proactive in changing out parts that were changed during PM’s. So for tracking purposes I would like to be able to open up my Parts Excel file and what every part that was installed over 6, 8, etc. months ago we will be notified.

Shardy,
I tried your code. I must have done something wrong. I pasted this into Visual Basic Editor:

Private Sub Workbook_Open()
Dim MyDate
Range("D1&quot.Select
MyDate = Selection
If Now() - MyDate > 180 Then
Range("A1:C1&quot.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub

MyDate = Selection
If Now() - MyDate > 180 Then
Range("A1:C1&quot.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub

I appreciate everyone’s help.

 

[JVFriederick]

Sounds like you are determined to use a macro.

What I wrote was "column D now being the date of last PM activity", ie when you changed the part. If you record that date, the advice I gave will work. You should add another column that states the MTBF of the part, and then a final column that calculates the elapsed days. Conditional format the elapsed days column, checking it against the MTBF column.

PS I've set up PM spreadsheets for quite a few years !!

 

[Sopman]

JVF,
I got your formula to work, but I had to add to Column E not the projected PM date but, =Now(). This works. Now when I open the Part file everything that is do will be highlighted.

I like to thank everyone for their time and assistance. This will help geatly!!!

 

[JVFriederick]