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