Hi Tim,

Thanks for your code but I'm getting an out of subscript error at the
following line:-
Set rng = ThisWorkbook.Sheets(Store).Cells(1)

Any suggestions?
--
Tracey @ BrisVegas


"Tim Williams" wrote:


Try placing this in the sheet code module
Untested, but should work...

Tim


Private Sub Worksheet_Activate()
UpdateFormats
End Sub

Sub UpdateFormats()
Dim c As Range, rng As Range
Dim Store, Pos, EmpName, Rating
Dim cIndex As Integer

'loop through each cell which needs formatting
For Each c In Me.Range("B2:E4").Cells

Store = c.EntireColumn.Cells(1).Value
Pos = c.EntireRow.Cells(1).Value
EmpName = c.Value
Rating = ""
cIndex = xlNone
'look for the employee in the relevant sheet
'(assumed sheets named "Store 1","Store 2" etc)
Set rng = ThisWorkbook.Sheets(Store).Cells(1)
Do While Len(rng.Value) 0
If rng.Value = Pos And rng.Offset(0, 1).Value = EmpName
Then
Rating = rng.Offset(0, 2).Value
Exit Do
End If
Set rng = rng.Offset(1, 0)
Loop

If Rating < "" Then
Select Case Rating
Case "High Potential": cIndex = 1
Case "High Value": cIndex = 2
'etc etc
End Select

End If

c.Interior.ColorIndex = cIndex

Next c
End Sub




On Jan 13, 5:53 pm, Tracey wrote:
I have 4 sheets that detail a group of Stores and their Personnel by their
Jobs and the Performance rating for last year and a fifth sheet that
summaries all of this data

e.g.
Sheet 1 = Store 1

Col A Col B Col C
Manager Bob High Potential
Meat Mgr Carol High Value
Bake Mgr Ted Performance Manage

Sheet 2 = Store 2 etc

Sheet 5 summarises all of the other 4 to give a complete list for the region
via a link to each individual store sheet
e.g.

Row Col A Col B Col C Col D Col E
Store 1 Store 2 Store 3 Store 4
Row 2 Manager Bob Alice Goofy Daisy
Row 3 Meat Mgr Carol Mickey Clarabelle Tracey
Row 4 Bake Mgr Ted Minnie Donald etc

I need to format cells B2 through E4 based on their Performance rating in
Col C from their Store sheet.

High Potential = Blue
High Value = Yellow
Performance Manage = Red
Promotable Next Lvl = Green
Promotable Current = Light Green
Too Soon to call = Blank

I hope this makes sense... thanks for your time Gord.

--
Tracey @ BrisVegas

.