Also, I forgot to add a check on the cell being formatted, to make
sure it had a value before looking it up on the other sheets.

For Each c In Me.Range("B2:E4").Cells
If Len(c.Value) 0 Then
'...rest of code...

End If
Next c

Tim


On Jan 14, 8:14*pm, "Tim Williams" wrote:
If it's not changing anything then it hasn't found a match on the "store x"
sheet,
or it's not getting triggered.

Try adding this:

Debug.Print Store, Pos, EmpName, cIndex

Immediately before the "Next c" line.

Or you can send me your workbook
tim j williams (no spaces)
at gmail dot com

Tim

"Tracey" wrote in message

...



Sorry Tim - I've corrected the out of range error and the code is running
ok
but it's not actually changing anything??
--
Tracey @ BrisVegas

"Tracey" wrote:

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

.- Hide quoted text -

- Show quoted text -