Keeping the colour of a referenced cell

[Mark]

Strange request, but here goes. At present I have an autoupdating
seating plan for my classes. It consists of a list of students on one
sheet and a "grid" showing the ddesks on another. Currently each "desk"
contains a formulae similar to "=List!B1"

So a colleague comes up with an idea to colour code her plan t gives
students feedback on behaviour and progress. However if we do this the
coloured allcated to each child will stay with the seat, losing the
flexibility that "Sort data by" affords. So my question is ...

Is it possible to allocate a colour to the "student" rather than the
"desk" and have this ccolour move with them if the list is resorted?

Thanks in advance

 

[JE McGimpsey]

Is it possible to allocate a colour to the "student" rather than the
"desk" and have this ccolour move with them if the list is resorted?

It certainly can be done using a macro, though the exact implementation
will depend on details you don't give.

Assume that your list of student names, on, say, List!B1:B40, is named
"Student_List" (select the list, and enter Student_List in the Name box
on the left side of the Formula Bar).

Assume that your seating chart, "Seating" has the grid, with each "seat"
filled with your =List!B1 -type formula.

You can then use the Seating sheet's _Activate event macro to color the
"seats" appropriately. Put this in the Seating sheet's code module
(right click on the Seating sheet tab and choose View Code):

Private Sub Worksheet_Activate()
Dim rCell As Range
Dim rFound As Range
For Each rCell In Cells.SpecialCells(xlCellTypeFormulas)
With rCell
Set rFound = Sheets("List").Range("Student_List").Find( _
What:=.Value, _
LookIn:=xlValues, _
lookat:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
.Interior.ColorIndex = rFound.Interior.ColorIndex
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
Next rCell
End Sub

Now whenever you switch to the Seating sheet, the colors will update
from those in Student_List.

 

[Mark]

Assume that your list of student names, on, say, List!B1:B40, is named
"Student_List" (select the list, and enter Student_List in the Name box
on the left side of the Formula Bar).

Assume that your seating chart, "Seating" has the grid, with each
"seat" filled with your =List!B1 -type formula.

Which it does ... : )

You can then use the Seating sheet's _Activate event macro to color the
"seats" appropriately.

(Snip) I'll give it a go! That is assuming the network manager has
allowed macros to be enabled on his precious system! (They are
cross-platform, aren't they?)

Now whenever you switch to the Seating sheet, the colors will update
from those in Student_List.

I really wanted the colours to be changed in either view, but that's
for another day!

Thanks loads