Won't make any difference when a colour is changed.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...

changing color does not trigger recalc

an udf will recalc only when a referenced argument
changes VALUE.

add Application.Volatile = True as the FIRST line in your UDF to make
it recalc whenever anything else is recalculated.

When using large numbers of calls
this will make your sheet 'sluggish', so use
Volatile sparingly.

Also your functions is not very efficient due to
all those if's. More efficient would be following:

Function FillColor(Cell As Range) As String
Static asNames$(1 To 56)
Dim idx As Integer

'Function will respond on all calculations
Application.Volatile

If asNames(1) = "" Then
'initialize static array
asNames(1) = "Black"
asNames(2) = "White"
asNames(3) = "Red"
asNames(4) = "Bright Green"
asNames(5) = "Blue"
asNames(6) = "Yellow"
asNames(7) = "Pink"
asNames(8) = "Turquoise"
asNames(9) = "Dark Red"
asNames(10) = "Green"
asNames(11) = "Dark Blue"
asNames(12) = "Dark Yellow"
asNames(13) = "Violet"
asNames(14) = "Teal"
asNames(15) = "Grey-25%"
asNames(16) = "Grey-50%"
asNames(33) = "Sky Blue"
asNames(34) = "Light Turquoise"
asNames(35) = "Light Green"
asNames(36) = "Light Yellow"
asNames(37) = "Pale Blue"
asNames(38) = "Rose"
asNames(39) = "Lavender"
asNames(40) = "Tan"
asNames(41) = "Light Blue"
asNames(42) = "Aqua"
asNames(43) = "Lime"
asNames(44) = "Gold"
asNames(45) = "Light Orange"
asNames(46) = "Orange"
asNames(47) = "Blue Gray"
asNames(48) = "Grey-40%"
asNames(49) = "Dark Teal"
asNames(50) = "Sea Green"
asNames(51) = "Dark Green"
asNames(52) = "Olive Green"
asNames(53) = "Brown"
asNames(54) = "Plum"
asNames(55) = "Indigo"
asNames(56) = "Grey-80%"
End If

idx = Cell.Cells(1).Interior.ColorIndex
Select Case idx
Case xlNone, xlAutomatic
FillColor = "none"
Case 1 To 16, 33 To 56
FillColor = asNames(idx)
Case Else
FillColor = "nonstd(" & idx & ")"
End Select

End Function







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


FredrikLyhagen wrote :

Function FillColor(Cell As Range) As String
Dim C As Long
C = Cell.Interior.ColorIndex
If C = 1 Then
FillColor = "Black"
ElseIf C = 9 Then
FillColor = "Dark Red"
ElseIf C = 3 Then
FillColor = "Red"
ElseIf C = 7 Then
FillColor = "Pink"
ElseIf C = 38 Then
FillColor = "Rose"
ElseIf C = 53 Then
FillColor = "Brown"
ElseIf C = 46 Then
FillColor = "Orange"
ElseIf C = 45 Then
FillColor = "Light Orange"
ElseIf C = 44 Then
FillColor = "Gold"
ElseIf C = 40 Then
FillColor = "Tan"
ElseIf C = 52 Then
FillColor = "Olive Green"
ElseIf C = 12 Then
FillColor = "Dark Yellow"
ElseIf C = 43 Then
FillColor = "Lime"
ElseIf C = 6 Then
FillColor = "Yellow"
ElseIf C = 36 Then
FillColor = "Light Yellow"
ElseIf C = 51 Then
FillColor = "Dark Green"
ElseIf C = 10 Then
FillColor = "Green"
ElseIf C = 50 Then
FillColor = "Sea Green"
ElseIf C = 4 Then
FillColor = "Bright Green"
ElseIf C = 35 Then
FillColor = "Light Green"
ElseIf C = 49 Then
FillColor = "Dark Teal"
ElseIf C = 14 Then
FillColor = "Teal"
ElseIf C = 42 Then
FillColor = "Aqua"
ElseIf C = 8 Then
FillColor = "Turquoise"
ElseIf C = 34 Then
FillColor = "Light Turquoise"
ElseIf C = 11 Then
FillColor = "Dark Blue"
ElseIf C = 5 Then
FillColor = "Blue"
ElseIf C = 41 Then
FillColor = "Light Blue"
ElseIf C = 33 Then
FillColor = "Sky Blue"
ElseIf C = 37 Then
FillColor = "Pale Blue"
ElseIf C = 55 Then
FillColor = "Indigo"
ElseIf C = 47 Then
FillColor = "Blue Gray"
ElseIf C = 13 Then
FillColor = "Violet"
ElseIf C = 54 Then
FillColor = "Plum"
ElseIf C = 39 Then
FillColor = "Lavender"
ElseIf C = 56 Then
FillColor = "Grey-80%"
ElseIf C = 16 Then
FillColor = "Grey-50%"
ElseIf C = 48 Then
FillColor = "Grey-40%"
ElseIf C = 15 Then
FillColor = "Grey-25%"
ElseIf C = 2 Then
FillColor = "White"
Else
FillColor = "NonStnd"
End If
Application.Calculate
End Function