Change color only if value has not already been entered

[lotr gollum]

I'm trying to create a spreadsheet with columns a, b, c, and d. "a" always has a value and I want columns c and d to change to a's color when they are given values. if "b" has a value when the data for c and d is entered, i want them to take b's color instead. The part I don't know how to do is that if i decide to give "b" a value, I don't want "c" or "d" to change colors if they already have values. e.g. if only a has a value when i give one to c, c will take a's color, but then if i give b a value then d, i want d to take b's color and c to retain a's color.

I hope i've made myself clear. I'm a bit of a noob at scripting in excel, but only help would be much appreciated. Thanks!

-master of the precious

[bhofsetz]

Can you post your workbook and what code you have working so far?

[lotr gollum]

K, here's my spreadsheet. I'll love you if you can help me figure this out.

[bhofsetz]

Paste this into your worksheet module.

Please Login or Register  to view this content.

[lotr gollum]

Hmm.... I plugged in the code but it doesn't seem to be working... maybe, I've just done something noobish. Anyway, I'd like to fix up not only the colors but the amounts also. I want it to have the first person's wage for a time, but once there's a replacement I want his new wage from that point on in a different color. You see what I mean now?

[lotr gollum]

ugg... here's the new xls file

[lotr gollum]

sorry for posting so much... anyway, i've got everything fixed up with this file except that i need the cells to retrieve their values from the replacement field ONLY if they do not already have a value. If they already have a value, the fields shouldn't change if i enter a new value in the replacement field. thx.

[bhofsetz]

So you want the wage and earnings columns from the point a Replacement employee is added forward to be the new color and calculate at the new rate. This will involve tweaking the formulas in each of the subsequent cells but not affect the formulas in the cells that are based on the current employe's wage.

This is more complicated that originally presented.

[lotr gollum]

Sorry, I thought I could figure out part of that on my own, but yes, the tricky part is going to be not tweaking the previous previous cells which are not based on the new employee's wage...

[bhofsetz]

Here are my thoughts on your problem.

Have a Worksheet_Change event handler that is triggered when you enter the hours worked for each pay period. This will check to see if a replacemnet has been entered in column D. If so then it will calculate the Earnings based on the replacements Hourly Rate (column E) and color the cells accordingly. If D is empty then it will calculate Earnings based on the original employee's Hourly Rate (Column C) and color those cells accordingly.

This method assumes that you will be entering Hours worked sequentially by pay period. If you are going to try and use this sheet for Budget analysis scenerios based on replacing certian people with others at different times then this approach won't work.

I can work on the proposed solution but if I knew the end use of the spreadsheet that would help.

[lotr gollum]

You hit the nail on the head. That's exactly what I'm trying to do, and yes, the hours will be entered sequentially for each pay period. If you don't mind helping me out with this that would be great. Thanks for the time you've put into this.

[bhofsetz]

Paste this into your sheet module

Please Login or Register  to view this content.

Let me know how it works for you.

[lotr gollum]

The code doesn't seem to be working for me. When I open the sheet I tell it to enable macros, but still nothing. The cells still take their values from column "E" if I plug in a value, regardless of previous amounts that have been entered. This is what I have...

[lotr gollum]

The point of the worksheet is this... Employee A works for 5 pay periods and for those 5 pay periods the hours and the $ amount will be blue and based on his hourly wage. Employee B comes to take his place for pay period 6 and on in the same row. His hours and $ will be green and based on his new wage from column "E." Does this help any?

[bhofsetz]

OK, I think I've got it now.

I had to change your original formula and remove your conditional formatting.

So start with the attached workbook and see what you think

[lotr gollum]

You are the man. Thanks bro. for all the work you put into this.

[lotr gollum]

Arrghhh... It works nicely for the rows that only have one digit, but for some reason it won't work with rows in the double or triple digits. Any other suggestions?

[lotr gollum]

Does anyone know how to make this macro work for rows in the double and triple digits? (bump)

[bhofsetz]

Sorry, I didn't do a thorough test before.

Replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[lotr gollum]

K, it runs perfectly now. You're still the man.