Gary,

You could use the worksheet's change event to do that. For example, make up the ten sets of seven
colored cells with the formatting that you want, and name them FormatSet1, FormatSet2,.....
FormatSet10.

If your cells "populated with a value" are in column A, code like this will copy the sets of
formatting and paste the formatting when you type in the 1, 2, .... 10 (you could use any value in
the name that you like....)

Copy the code, right-click the sheet tab, select "View Code" and paste the code into the window that
appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub
On Error Resume Next
Range("FormatSet" & Target.Value).Copy
Target(1, 2).PasteSpecial xlPasteFormats
Target(2, 1).Select
End Sub


--
HTH,
Bernie
MS Excel MVP


"Gary" wrote in message
...
I have a spreadsheet (shift patterns) which have cells populated with a value.
What I want to do is, if the cell value = "" then copy a 7 cells which are
populated with different colours.
there are 10 different patterns. I want the finished spread sheet to display
a colour in each cell which equals a specific work pattern, i.e a red cell
equals a 8 until 5 shift pattern.
Any help would be very much appriciated. Gary