Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes linked to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for each
cell to be formatted takes up to a second or two. For the sheet with 450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype cell to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is fine if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _
"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at the
beginning of the code and Application.ScreenUpdating = True at the end of the
code to speed up things.

I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes linked to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for each
cell to be formatted takes up to a second or two. For the sheet with 450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype cell to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is fine if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _
"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

Thanks for the quick reply Robert.

But conditional formating won't work as far as I know because I don't want
to change any cell values, just the colour, and conditional formatting as I
undersatnd it requires a change in a value.

Screenupdating is switched off then switched on again already. I can tell
how long each cell is taking to be formatted as I am changing the
application.status bar when a new row is selected.

Thanks anyway.

Any one else got any ideas??

Chris
"cmart02" wrote in message
...
Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at the
beginning of the code and Application.ScreenUpdating = True at the end of
the
code to speed up things.

I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes linked
to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for each
cell to be formatted takes up to a second or two. For the sheet with 450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype cell
to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is fine
if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _

"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am
doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

Hi Chris,

What do you have entered in the Paytype cells?

Are all the Paytype cells to be coloured identically?


---
Regards,
Norman



"Chris Ferguson" wrote in message
...
Thanks for the quick reply Robert.

But conditional formating won't work as far as I know because I don't want
to change any cell values, just the colour, and conditional formatting as
I undersatnd it requires a change in a value.

Screenupdating is switched off then switched on again already. I can tell
how long each cell is taking to be formatted as I am changing the
application.status bar when a new row is selected.

Thanks anyway.

Any one else got any ideas??

Chris
"cmart02" wrote in message
...
Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at the
beginning of the code and Application.ScreenUpdating = True at the end of
the
code to speed up things.

I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we
can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes linked
to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for
each
cell to be formatted takes up to a second or two. For the sheet with
450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype
cell to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is
fine if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _

"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am
doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

Hi
What I have is a blank sheet with names down the side and paytype along the
top. I want the users to be able to tab into only the paytypes that are
relevant to the particular employee. So to help the user each relevant
paytype is unlocked and coloured and the column is set to be 10.

So there is no value in the paytype yet and each paytype relevant to the
employee is to be identically coloured. There may be upto 100 different
paytypes for the company involved but each employee may have only 10 that
are relevant. So only those 10 should be accessable to the user.

The employees are further split into departments, each with their own
paytypes, which is why there can be up to 100 paytypes. I only put onto
this sheet the employees relevant to the department. I don't want all
paytypes visible as the width of the sheet would be unusable which is why I
first hide all of the columns and then only set the width for those paytypes
that are relevant. In practice this normally leaves me with about 15
columns as some employees can work for more than one department.

Paytype would be things like, Salary, Overtime1, Overtime 1.5, Overtime2
etc.
Just to complicate things further, I use this timesheet to create a csv file
which is then used to import into a payroll program.

Does this background help?

Chris

ps I am currently trying to create a string with the refer's to part of the
add name and append each cell reference to that string. But at the moment I
haven't quite got it right, but will keep trying.



"Norman Jones" wrote in message
...
Hi Chris,

What do you have entered in the Paytype cells?

Are all the Paytype cells to be coloured identically?


---
Regards,
Norman



"Chris Ferguson" wrote in message
...
Thanks for the quick reply Robert.

But conditional formating won't work as far as I know because I don't
want to change any cell values, just the colour, and conditional
formatting as I undersatnd it requires a change in a value.

Screenupdating is switched off then switched on again already. I can
tell how long each cell is taking to be formatted as I am changing the
application.status bar when a new row is selected.

Thanks anyway.

Any one else got any ideas??

Chris
"cmart02" wrote in message
...
Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at
the
beginning of the code and Application.ScreenUpdating = True at the end
of the
code to speed up things.

I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we
can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes
linked to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change
the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for
each
cell to be formatted takes up to a second or two. For the sheet with
450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype
cell to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is
fine if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _

"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am
doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

Hi Chris,

Would it be possible to implement a different design logic?

Consider using dependent lists so that each employee sees only relevant
paytype categories. Feed the input data to your record / calculation sheets.

For examples using dependent lists see:

http://www.contextures.com/xlDataVal02.html

and:

http://www.xldynamic.com/source/xld.Dropdowns.html

With the current configuration, I think the colouration of the cells
requires knowledge of the rules that govern paytype eligibility.

--
Regards,
Norman


"Chris Ferguson" wrote in message
...
Hi
What I have is a blank sheet with names down the side and paytype along
the top. I want the users to be able to tab into only the paytypes that
are relevant to the particular employee. So to help the user each
relevant paytype is unlocked and coloured and the column is set to be 10.

So there is no value in the paytype yet and each paytype relevant to the
employee is to be identically coloured. There may be upto 100 different
paytypes for the company involved but each employee may have only 10 that
are relevant. So only those 10 should be accessable to the user.

The employees are further split into departments, each with their own
paytypes, which is why there can be up to 100 paytypes. I only put onto
this sheet the employees relevant to the department. I don't want all
paytypes visible as the width of the sheet would be unusable which is why
I first hide all of the columns and then only set the width for those
paytypes that are relevant. In practice this normally leaves me with
about 15 columns as some employees can work for more than one department.

Paytype would be things like, Salary, Overtime1, Overtime 1.5, Overtime2
etc.
Just to complicate things further, I use this timesheet to create a csv
file which is then used to import into a payroll program.

Does this background help?

Chris

ps I am currently trying to create a string with the refer's to part of
the add name and append each cell reference to that string. But at the
moment I haven't quite got it right, but will keep trying.



"Norman Jones" wrote in message
...
Hi Chris,

What do you have entered in the Paytype cells?

Are all the Paytype cells to be coloured identically?


---
Regards,
Norman



"Chris Ferguson" wrote in message
...
Thanks for the quick reply Robert.

But conditional formating won't work as far as I know because I don't
want to change any cell values, just the colour, and conditional
formatting as I undersatnd it requires a change in a value.

Screenupdating is switched off then switched on again already. I can
tell how long each cell is taking to be formatted as I am changing the
application.status bar when a new row is selected.

Thanks anyway.

Any one else got any ideas??

Chris
"cmart02" wrote in message
...
Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at
the
beginning of the code and Application.ScreenUpdating = True at the end
of the
code to speed up things.

I am recruting new members for my forum below... The site went live
this
weekend and if you wish to take part, please, join me there so that we
can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes
linked to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change
the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for
each
cell to be formatted takes up to a second or two. For the sheet with
450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype
cell to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is
fine if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _

"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R15C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am
doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris

I am not sure I get this. Are you asking for some code to just sweep through
the data colouring it?

--
HTH

Bob Phillips

"Chris Ferguson" wrote in message
...
Thanks for the quick reply Robert.

But conditional formating won't work as far as I know because I don't want
to change any cell values, just the colour, and conditional formatting as
I
undersatnd it requires a change in a value.

Screenupdating is switched off then switched on again already. I can tell
how long each cell is taking to be formatted as I am changing the
application.status bar when a new row is selected.

Thanks anyway.

Any one else got any ideas??

Chris
"cmart02" wrote in message
...
Cris,

To chance the colors your best bet is conditional formatting.

Also, in your code, you can use Application.ScreenUpdating = False at
the
beginning of the code and Application.ScreenUpdating = True at the end
of
the
code to speed up things.

I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we
can
exchange some ideas. :-D

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"Chris Ferguson" wrote:

Hi
I have a sheet with peoples names in column A, anything from 30 to 450
names.
In the columns from B for up to 100 columns there will be Paytpes
linked
to
each person. These columns initially are hidden.
What I then do is for each persons relevant paytype I want to change
the
colour of the cell, unlock it and change its width to 10.

This is working well at the moment but it is quite slow and each for
each
cell to be formatted takes up to a second or two. For the sheet with
450
people and anything up to 10 paytypes each, this is quite a long time.

My thoughts are to have a named range, and add each employee/paytype
cell
to
that named range and then format the named range at the end.
I can see how to create a named range and refer to cells and this is
fine
if
you know at the time of creating the named range which cells are to be
refered to.

ActiveWorkbook.Names.Add Name:="TestNamedRange", RefersToR1C1:= _


"=Timesheet!R4C4,Timesheet!R7C4,Timesheet!R10C4,Ti mesheet!R12C6,Timesheet!R1
5C4"

BUT my questions are :-
How can I append more cells to the end of the list of cells?
Is there a limit to the number of cells that can be added?
Will it be any quicker than formating the cells individually as I am
doing
at the moment?

Can anyone point me in the right direction.

many Thanks

Chris