Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color on a range of cells
Hi all.
I have a spreadsheet that I distribute to up to 4 people in my office (depending on what account we are on) These recipients fill out data in specific order that is based on the prior persons infomation input. My spreadsheet e-mails the file to the next person in line and I have found that because the spreadsheet is not password protected, some of the users have entered data in the wrong cells and thus causing a major problem in reporting. What I want to do is color the specific cells availible for that user based on the name of each User. In cell D11- G11 I have range names(Person1,Person2...)set up with the users names and I have a seperate cell C3 (CurrentName) that says whos turn it is to enter data. The users availble cells to enter data in is located directly under their name, but goes down about 100 rows so the chance of an error increases when the top line disapears off the top of the screen. (Freezing that cell won't work in this application) How can I change the color of these 100 cells so the user has a visual reference as to what cells they are working with. The spreadsheet is set up with a macro button that calculates the final info and sends an e-mail to a dedicated server for backup filing. When this macro is preformed I cycle the Users name and e-mail the saved file to the next user and then want to change the cell colors in line with the next users name. Can anyone help me with this? Thanks and Have a Happy new Year. Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color on a range of cells
Pete,
Try Data Validation. Select cells D12:G111 (or greater) Goto menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$C$3=D$11 (note the $ etc) Click the Format button Select a font colour Select the pattern tab Select a cell colour OK OK -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... Hi all. I have a spreadsheet that I distribute to up to 4 people in my office (depending on what account we are on) These recipients fill out data in specific order that is based on the prior persons infomation input. My spreadsheet e-mails the file to the next person in line and I have found that because the spreadsheet is not password protected, some of the users have entered data in the wrong cells and thus causing a major problem in reporting. What I want to do is color the specific cells availible for that user based on the name of each User. In cell D11- G11 I have range names(Person1,Person2...)set up with the users names and I have a seperate cell C3 (CurrentName) that says whos turn it is to enter data. The users availble cells to enter data in is located directly under their name, but goes down about 100 rows so the chance of an error increases when the top line disapears off the top of the screen. (Freezing that cell won't work in this application) How can I change the color of these 100 cells so the user has a visual reference as to what cells they are working with. The spreadsheet is set up with a macro button that calculates the final info and sends an e-mail to a dedicated server for backup filing. When this macro is preformed I cycle the Users name and e-mail the saved file to the next user and then want to change the cell colors in line with the next users name. Can anyone help me with this? Thanks and Have a Happy new Year. Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color on a range of cells
Thank you that worked great. Now what if I were to really
get anal and say this still may not be a absolute solver of my problem. I want to protect the entire sheet except for the range of cells that are active. So user 2 can input data into users 3 column. Pete -----Original Message----- Pete, Try Data Validation. Select cells D12:G111 (or greater) Goto menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$C$3=D$11 (note the $ etc) Click the Format button Select a font colour Select the pattern tab Select a cell colour OK OK -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... Hi all. I have a spreadsheet that I distribute to up to 4 people in my office (depending on what account we are on) These recipients fill out data in specific order that is based on the prior persons infomation input. My spreadsheet e-mails the file to the next person in line and I have found that because the spreadsheet is not password protected, some of the users have entered data in the wrong cells and thus causing a major problem in reporting. What I want to do is color the specific cells availible for that user based on the name of each User. In cell D11- G11 I have range names(Person1,Person2...)set up with the users names and I have a seperate cell C3 (CurrentName) that says whos turn it is to enter data. The users availble cells to enter data in is located directly under their name, but goes down about 100 rows so the chance of an error increases when the top line disapears off the top of the screen. (Freezing that cell won't work in this application) How can I change the color of these 100 cells so the user has a visual reference as to what cells they are working with. The spreadsheet is set up with a macro button that calculates the final info and sends an e-mail to a dedicated server for backup filing. When this macro is preformed I cycle the Users name and e-mail the saved file to the next user and then want to change the cell colors in line with the next users name. Can anyone help me with this? Thanks and Have a Happy new Year. Pete . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color on a range of cells
Pete,
You'd need VBA. Up for it? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... Thank you that worked great. Now what if I were to really get anal and say this still may not be a absolute solver of my problem. I want to protect the entire sheet except for the range of cells that are active. So user 2 can input data into users 3 column. Pete -----Original Message----- Pete, Try Data Validation. Select cells D12:G111 (or greater) Goto menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$C$3=D$11 (note the $ etc) Click the Format button Select a font colour Select the pattern tab Select a cell colour OK OK -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... Hi all. I have a spreadsheet that I distribute to up to 4 people in my office (depending on what account we are on) These recipients fill out data in specific order that is based on the prior persons infomation input. My spreadsheet e-mails the file to the next person in line and I have found that because the spreadsheet is not password protected, some of the users have entered data in the wrong cells and thus causing a major problem in reporting. What I want to do is color the specific cells availible for that user based on the name of each User. In cell D11- G11 I have range names(Person1,Person2...)set up with the users names and I have a seperate cell C3 (CurrentName) that says whos turn it is to enter data. The users availble cells to enter data in is located directly under their name, but goes down about 100 rows so the chance of an error increases when the top line disapears off the top of the screen. (Freezing that cell won't work in this application) How can I change the color of these 100 cells so the user has a visual reference as to what cells they are working with. The spreadsheet is set up with a macro button that calculates the final info and sends an e-mail to a dedicated server for backup filing. When this macro is preformed I cycle the Users name and e-mail the saved file to the next user and then want to change the cell colors in line with the next users name. Can anyone help me with this? Thanks and Have a Happy new Year. Pete . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing color of a range of cells dending up on a value in anothr | New Users to Excel | |||
Changing the color of highlighted cells | Excel Discussion (Misc queries) | |||
Changing the color of a selected range | Excel Discussion (Misc queries) | |||
color changing of cells | Excel Discussion (Misc queries) | |||
Changing color of cells | Excel Programming |