Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

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

Amending Cell formatting with VBA
 

Hi Norman.
I am always open to different ideas and approaches.
Any input would be welcome.

I am reading data from payroll files.
The paytype table is a company wide table and has a reference number and a
description, with default rates etc.
The employee file has a table listing which paytypes are associated with
each employee.
So the employee 1 would have paytype 1,3,5 ,6,8,12,34 etc.

This gives me a table read by odbc from the payroll files of employee
number, paytype number.

My thought was to have the timesheet laid out with the names in column A and
paytypes as columns. Then unlock for employees1 columns2,4,6,7,9,13,35 etc,
and all of the pther paytype for employee 1 being locked so with the sheet
being protected and unlocked cells only being selectable the user could
easily tab from one relevnat paytype to the next..
This would produce a grid into which the user could enter the number of
hours worked by each employee for each paytype.

There are other sheets for amending employee details and adding new
employees etc in which I have used dependent lists for selecting departments
and paytypes when setting new employees up and subsequently amending them. I
have also set up userforms to input new employees and amending exisiting
employees. But I can't seem to get away from a grid of employees and
paytypes being the quickest/easiest way for users to enter timesheet
information.

As I see it using dependent lists would make the users select each employee,
then select a paytype and then enter a value. Rather than entering a value
and tabing to the next relevant paytype. Hav I missed something in
dependent lists?

This isn't where I had hoped the thread would lead me. Does this mean that
creating a named range and then applying formatting to that named range is
not the way that I should be heading? As I think I said earlier formatting
each relevant cell for each employee currently works, but takes such a long
time to format each cell individually that I was trying to find a way to
create a non contiguous named range then apply formatting to the whole
range.

Chris

"Norman Jones" wrote in message
...
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

Amending Cell formatting with VBA
 

Thanks Guys.
Your comments helped me look at this with fresh eyes and review what I had
already done.
I have managed to speed this up by creating a variable to which I append the
cell's address's. I had hoped that I could then format all of the cells at
once but there seems to be limit on the number of characters help in a
variable when using that variable to create a named range. But still I can
add approximately 40 cells to the named range and format those all at once,
which speeds things up by about a factor of 40 which is quite noticable.

Thanks for all your input

Chris


"Chris Ferguson" wrote in message
...
Hi Norman.
I am always open to different ideas and approaches.
Any input would be welcome.

I am reading data from payroll files.
The paytype table is a company wide table and has a reference number and a
description, with default rates etc.
The employee file has a table listing which paytypes are associated with
each employee.
So the employee 1 would have paytype 1,3,5 ,6,8,12,34 etc.

This gives me a table read by odbc from the payroll files of employee
number, paytype number.

My thought was to have the timesheet laid out with the names in column A
and paytypes as columns. Then unlock for employees1
columns2,4,6,7,9,13,35 etc, and all of the pther paytype for employee 1
being locked so with the sheet being protected and unlocked cells only
being selectable the user could easily tab from one relevnat paytype to
the next..
This would produce a grid into which the user could enter the number of
hours worked by each employee for each paytype.

There are other sheets for amending employee details and adding new
employees etc in which I have used dependent lists for selecting
departments and paytypes when setting new employees up and subsequently
amending them. I have also set up userforms to input new employees and
amending exisiting employees. But I can't seem to get away from a grid of
employees and paytypes being the quickest/easiest way for users to enter
timesheet information.

As I see it using dependent lists would make the users select each
employee, then select a paytype and then enter a value. Rather than
entering a value and tabing to the next relevant paytype. Hav I missed
something in dependent lists?

This isn't where I had hoped the thread would lead me. Does this mean
that creating a named range and then applying formatting to that named
range is not the way that I should be heading? As I think I said earlier
formatting each relevant cell for each employee currently works, but takes
such a long time to format each cell individually that I was trying to
find a way to create a non contiguous named range then apply formatting to
the whole range.

Chris

"Norman Jones" wrote in message
...
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