how do I carry cell colors?

[Guest]

I'm trying to set up an automatic work schedule.
I have 10 names (cells are color shaded by a unique color)
How do I carry that individual's cell color in to a new cell with some sort
of formula? or what is the formula for cell color shades?

 

[JE McGimpsey]

You can't use formulas to change cell/font color. Formulas only return
values to their calling cells.

If you're using XL2007, you can set up conditional formatting for each
of the 10 names.

If you're not, you'll need to use an event macro to do it automatically.
For instance, if your copied cells are in J1:Z100:

Private Sub Worksheet_Calculate()
Dim rFormulas As Range
Dim rCell As Range

On Error Resume Next
Set rFormulas = Range("J1:Z100").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulas Is Nothing Then
For Each rCell In rFormulas
Select Case rCell.Text
Case "Smith"
rCell.Interior.ColorIndex = 3
Case "Jones"
rCell.Interior.ColorIndex = 5
Case "Johnson"
rCell.Interior.ColorIndex = 7
'Additional cases here...
Case Else
rCell.Interior.ColorIndex = xlColorIndexNone
End Select
Next rCell
End If
End Sub

 

[Guest]

That's it! Thank you for the code!