I'm currently establishing a tracking system for our company (we service heavy equipment) and I'd love to have this spreadsheet done today. I've managed to figure out other conditional formatting necessary, however this one has me stumped:
I need to highlight the appropriate cells when "Current Machine Hours on Record" (K2) are "250 hours" (M2) ABOVE the "Machine hours at last service" (H2)
ANY assistance would be appreciated!
Thanks in advance.
Last edited by MMolloy; 12-05-2014 at 10:23 PM.
What are "the appropriate cells"? It would be very helpful if you attach your file.
To attach a file:
- Under the text box where you type your reply click the Go Advanced button.
- In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
- In the pop-up, click Add then Select to select a file from your local drive
- Click the Upload button to upload the file
- Click Done to attach it.
It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.
Here are step-by-step instructions with screen shots
ThanksHope this helps. *edit* I should also add that by saying "appropriate cells", I simply meant I need to highlight the row.
Last edited by MMolloy; 12-05-2014 at 05:17 PM.
That helps a LOT! Thanks.
You already have two rules in place that will change the fill color of rows based on time since last service. (Those need to be cleaned up but I can help with that.) For the rule based on machine hours, which one is supposed to take precedence if both are true? What color do you want to use for the machine hours rule?
I'm sure it looks like a distaster to an educated eye! I basically just need the row to change red if it had been either 250 hours since the last service, or if it has been 180 days since the last service - which ever comes first.
The yellow formatting comes into play as a 'warning' that a service needs to be scheduled soon (150 days since last service).
Its embarrassing to let anyone see my awful Excel "skills".
Last edited by MMolloy; 12-05-2014 at 07:28 PM.
You have individual rules set up for each row. If you write the rule properly you can use one for all rows.
There are now three rules, and they apply to rows 2-100.
I left the first rule alone.
The "blank cell" rule now cover rows 2-100. It adds logic that assumes that if cell in column A is blank, it's OK for the rest of the row to be blank. If the cell in column A is non-blank, then any blank cells in that row will be shaded.
I modified your "red" rule to extend to rows 2-100, and apply only to cells in rows with a non-blank cell in column A. I also added the condition to check for the number of hours since the last service and compare to the number in column M.
I modified the "yellow" rule to extend to rows 2-100, and apply only to cells in rows with a non-blank cell in column A.
You'll note that in the CF formulas, I removed the $ from the row number (changed $F$2 to $F2). That is essential to get this to work in every row.
Let me know if this is what you are trying to get to. Conditional formatting starting with Excel 2007 changed quite a bit. It is much more powerful, but also difficult for beginners to learn. This might be helpful.
I love you so much, it hurts.
Can I bake you some cookies, or wash your car?
Thank you!!!
Thanks for the offer
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks