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