maybe a Macro?

[Cindy Conover]

I have use Excel in a very limited capacity for years so I am going to try
to explain what I want the best I can. I have a spreadsheet that I created
to keep track of the services and inspections of company vehicles. This
works great but every week I have to go through each unit to high light the
units that need attention. The spreadsheet looks like the following:

Unit (B1), Last Pm Date (D1), Last PM Miles (E1), Last DOT Date (F1),Current
Miles (G1), Next Pm Date (I1), Next Pm Mileage (J1), Next DOT date (K1)

What I do now is every week go through and change the text red on the areas
the need attention. (I1, J1 or K1) Is there a macro or something that would
change the color from black to red or visa versa when I update the current
info?

TIA

Cindy

 

[gumby]

I have use Excel in a very limited capacity for years so I am going to try
to explain what I want the best I can. I have a spreadsheet that I created
to keep track of the services and inspections of company vehicles. This
works great but every week I have to go through each unit to high light the
units that need attention. The spreadsheet looks like the following:

Unit (B1), Last Pm Date (D1), Last PM Miles (E1), Last DOT Date (F1),Current
Miles (G1), Next Pm Date (I1), Next Pm Mileage (J1), Next DOT date (K1)

What I do now is every week go through and change the text red on the areas
the need attention. (I1, J1 or K1) Is there a macro or something that would
change the color from black to red or visa versa when I update the current
info?

TIA

Cindy

What determines if it needs to be highlighted? Do you have a set
standard. You could just use conditional formating.

David

 

[Cindy Conover]

Sorry, I should have included that.
I1 is 90days later than D1
J1 is 20,000 miles later than E1
K1 is 360 days later than F1
so I need to know if any unit is past the three columns miles, or time.

Cindy

 

[Jordon]

Sorry, I should have included that.
I1 is 90days later than D1
J1 is 20,000 miles later than E1
K1 is 360 days later than F1
so I need to know if any unit is past the three columns miles, or time.

Select I1. On the format menu select Conditional Formatting. Change it
to Formula Is... =IF(I1>D1+89,TRUE) and format with a red font or cell
background. Do something similar to J1 and K1.

You could also have a couple of conditions, one to mark the cell
background yellow when it's 2 weeks away from the 90 day and yearly
inspections and have it change to red when it meets that date.

 

[Stan Brown]

Thu, 17 May 2007 14:02:46 -0500 from Cindy Conover

What I do now is every week go through and change the text red on the areas
the need attention. (I1, J1 or K1) Is there a macro or something that would
change the color from black to red or visa versa when I update the current
info?

Format > Conditional Formatting and select the "formula" option.

 

[Cindy Conover]

Jordon,

Thank you so much, this opens a whole new group of possiblities for me.
Keep up the good work.

Cindy

"Jordon"

 

[Jordon]

Jordon,

Thank you so much, this opens a whole new group of possibilities for me.
Keep up the good work.

I'm in the trucking business. I've created a few spreadsheets that deal
with routine maintenance and inspections for vehicles.

 

[Cindy Conover]

So you know how important it is not to miss the DOT date. ;o)

Cindy

 

[Jordon]

So you know how important it is not to miss the DOT date. ;o)

If we're only talking a week or two it's not that big a deal. During a
DOT compliance review they look for patterns of bad behavior. If a
vehicle or two goes extra time between services or inspection every so
often they probably wouldn't even write it down unless there's a bunch
of other stuff that they find wrong. I've been through 5 of them in my
time and they've all been very understanding. And just because they
write you up in a review for a few things doesn't mean you'll get an
unsatisfactory rating.

20,000 miles between services? What type of vehicles are those?

 

[Cindy Conover]

T800 & W9L. 13 weeks or 20,000 miles whatever comes first. I have been
lucky enough not to have had to go through a DOT audit. But I have had the
DOT state mileage audit before.
Is that a KW on your website?

Cindy

 

[Jordon]

Is that a KW on your website?

Yep. We have about 10 new ones. And few Petes, Macks, Freightliners and
one mechanics nightmare, a Volvo.

 

[Cindy Conover]

I work in a KW dealership on the rental and lease side.

 

[Cindy Conover]

Jordon,

I have the mileage column working beatifully. But I am having a problem with
the DOT due date. The format in the due column(J4) is F4+365. If I try to
do a conditional format I turn the whole cloumn "blue". Do I need to have
today's date in the format somewhere?

cindy

I work in a KW dealership on the rental and lease side.

 

[Jordon]

Jordon,

I have the mileage column working beatifully. But I am having a problem with
the DOT due date. The format in the due column(J4) is F4+365. If I try to
do a conditional format I turn the whole cloumn "blue". Do I need to have
today's date in the format somewhere?

Copy the relevant formulas into a new workbook and sent it to me.

jordon (at) grahamtrucking (dot) com

 

[Cindy Conover]

sent it

Copy the relevant formulas into a new workbook and sent it to me.

jordon (at) grahamtrucking (dot) com