| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell colour when cell data changes
One way using CF:
Create a background copy of the worksheet at each refreshment and apply CF with this formula: =A1<background!A1 Regards, Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Hi I've built a resource planning spreadsheet that is populated by macros. In each month (in columns) it populates with 1 if there is a person in the position or may be blank or 0 if the position is vacant. The spreadsheet goes out to 2014 monthly. The 12 months of each year are colour coded as one colour. I have been asked to have the cell colour change if the user changes the value of the cell. The spreadsheet will be periodically refreshed by the macro and saved as a new version so the orginal colours need to be reinstated with and changes then causing the cell to be changed. I can't get it to work with conditional formatting, any suggestions? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change cell colour when cell data changes
Sorry, Don, I don't understand, what is the problem with my post. Please,
explain it! Stefi €˛Don Guillett€¯ ezt Ć*rta: Please append to threads instead of starting a new one. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stefi" wrote in message ... One way using CF: Create a background copy of the worksheet at each refreshment and apply CF with this formula: =A1<background!A1 Regards, Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Hi I've built a resource planning spreadsheet that is populated by macros. In each month (in columns) it populates with 1 if there is a person in the position or may be blank or 0 if the position is vacant. The spreadsheet goes out to 2014 monthly. The 12 months of each year are colour coded as one colour. I have been asked to have the cell colour change if the user changes the value of the cell. The spreadsheet will be periodically refreshed by the macro and saved as a new version so the orginal colours need to be reinstated with and changes then causing the cell to be changed. I can't get it to work with conditional formatting, any suggestions? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change cell colour when cell data changes
Your question is still ambiguous: what should happen if user changes the
original zero value to 0.5? Should color be yellow in order to satisfy the 1st condition ("anything from blank or zero to 1.0 cell colour is yellow") or red in order to satisfy the 2nd condition (User changed to a value between zero and 10.0)? Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: I'm not sure what you mean either. I don't have a problem with refreshing to the original colour. What I can't work out how to do is to change the cell from the original colour to a new colour if the value in the cell changes. I tried one of the other options mentioned in someone elses post but as soon as I enter on the cell it changes which I don't want. It only needs to change under say the following example: orginal value = anything from blank or zero to 1.0 cell colour is yellow Users changes to anything from blank, zero to 10.0 colour is now red Any ideas? "Stefi" wrote: Sorry, Don, I don't understand, what is the problem with my post. Please, explain it! Stefi €˛Don Guillett€¯ ezt Ć*rta: Please append to threads instead of starting a new one. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stefi" wrote in message ... One way using CF: Create a background copy of the worksheet at each refreshment and apply CF with this formula: =A1<background!A1 Regards, Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Hi I've built a resource planning spreadsheet that is populated by macros. In each month (in columns) it populates with 1 if there is a person in the position or may be blank or 0 if the position is vacant. The spreadsheet goes out to 2014 monthly. The 12 months of each year are colour coded as one colour. I have been asked to have the cell colour change if the user changes the value of the cell. The spreadsheet will be periodically refreshed by the macro and saved as a new version so the orginal colours need to be reinstated with and changes then causing the cell to be changed. I can't get it to work with conditional formatting, any suggestions? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change cell colour when cell data changes
It would be a little bit complicated to describe the solution. Could you give
an address where to send a sample XLS? Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Sorry, the intention of the spreasheet is 1.0 means there is one full time person in the job, 0.5 means there is a half time person in the job i.e the work 2 and a half days a week compared to 5 days a week. Likewise if someone works 4 days a week they would 0.8. As people are going to be leaving, going partime, working on other jobs the user then changes the value to be the new working time eg. if Bob currently works full time each month would be populated with 1.0 when the data is loaded. If he goes to 4 days a week the user would change the value to 0.8 each month and if he quit the user would change the value to 0 or blank. What I want to do is have any changes the user does change the cell colour to highlight there has been a change - this is important as we are talking about approx 2,000 people. Does this make more sense? "Stefi" wrote: Your question is still ambiguous: what should happen if user changes the original zero value to 0.5? Should color be yellow in order to satisfy the 1st condition ("anything from blank or zero to 1.0 cell colour is yellow") or red in order to satisfy the 2nd condition (User changed to a value between zero and 10.0)? Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: I'm not sure what you mean either. I don't have a problem with refreshing to the original colour. What I can't work out how to do is to change the cell from the original colour to a new colour if the value in the cell changes. I tried one of the other options mentioned in someone elses post but as soon as I enter on the cell it changes which I don't want. It only needs to change under say the following example: orginal value = anything from blank or zero to 1.0 cell colour is yellow Users changes to anything from blank, zero to 10.0 colour is now red Any ideas? "Stefi" wrote: Sorry, Don, I don't understand, what is the problem with my post. Please, explain it! Stefi €˛Don Guillett€¯ ezt Ć*rta: Please append to threads instead of starting a new one. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stefi" wrote in message ... One way using CF: Create a background copy of the worksheet at each refreshment and apply CF with this formula: =A1<background!A1 Regards, Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Hi I've built a resource planning spreadsheet that is populated by macros. In each month (in columns) it populates with 1 if there is a person in the position or may be blank or 0 if the position is vacant. The spreadsheet goes out to 2014 monthly. The 12 months of each year are colour coded as one colour. I have been asked to have the cell colour change if the user changes the value of the cell. The spreadsheet will be periodically refreshed by the macro and saved as a new version so the orginal colours need to be reinstated with and changes then causing the cell to be changed. I can't get it to work with conditional formatting, any suggestions? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change cell colour when cell data changes
Then in which case do you want it change to yellow?
€˛Bec G (Oz)€¯ ezt Ć*rta: Stefi To clarify I just realised in my original post I had the following User changes to anything from blank, zero to 10.0 colour is now red this should actually read User changes to anything from blank, zero to 1.0 colour is now red email address is Thanks for your help on this "Stefi" wrote: It would be a little bit complicated to describe the solution. Could you give an address where to send a sample XLS? Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Sorry, the intention of the spreasheet is 1.0 means there is one full time person in the job, 0.5 means there is a half time person in the job i.e the work 2 and a half days a week compared to 5 days a week. Likewise if someone works 4 days a week they would 0.8. As people are going to be leaving, going partime, working on other jobs the user then changes the value to be the new working time eg. if Bob currently works full time each month would be populated with 1.0 when the data is loaded. If he goes to 4 days a week the user would change the value to 0.8 each month and if he quit the user would change the value to 0 or blank. What I want to do is have any changes the user does change the cell colour to highlight there has been a change - this is important as we are talking about approx 2,000 people. Does this make more sense? "Stefi" wrote: Your question is still ambiguous: what should happen if user changes the original zero value to 0.5? Should color be yellow in order to satisfy the 1st condition ("anything from blank or zero to 1.0 cell colour is yellow") or red in order to satisfy the 2nd condition (User changed to a value between zero and 10.0)? Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: I'm not sure what you mean either. I don't have a problem with refreshing to the original colour. What I can't work out how to do is to change the cell from the original colour to a new colour if the value in the cell changes. I tried one of the other options mentioned in someone elses post but as soon as I enter on the cell it changes which I don't want. It only needs to change under say the following example: orginal value = anything from blank or zero to 1.0 cell colour is yellow Users changes to anything from blank, zero to 10.0 colour is now red Any ideas? "Stefi" wrote: Sorry, Don, I don't understand, what is the problem with my post. Please, explain it! Stefi €˛Don Guillett€¯ ezt Ć*rta: Please append to threads instead of starting a new one. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stefi" wrote in message ... One way using CF: Create a background copy of the worksheet at each refreshment and apply CF with this formula: =A1<background!A1 Regards, Stefi €˛Bec G (Oz)€¯ ezt Ć*rta: Hi I've built a resource planning spreadsheet that is populated by macros. In each month (in columns) it populates with 1 if there is a person in the position or may be blank or 0 if the position is vacant. The spreadsheet goes out to 2014 monthly. The 12 months of each year are colour coded as one colour. I have been asked to have the cell colour change if the user changes the value of the cell. The spreadsheet will be periodically refreshed by the macro and saved as a new version so the orginal colours need to be reinstated with and changes then causing the cell to be changed. I can't get it to work with conditional formatting, any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change cell colour | Excel Discussion (Misc queries) | |||
| change cell colour due to a specific data set | Excel Discussion (Misc queries) | |||
| Change Colour on a Cell after another cell has been changed | Excel Discussion (Misc queries) | |||
| change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
| How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
Linear Mode
