Changing color on a range of cells

[Pete]

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

 

[Bob Phillips]

Pete,

Try Data Validation.

Select cells D12:G111 (or greater)
Goto menu Format>Conditional 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]

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

 

[Bob Phillips]

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)