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