Hi,

I have written a little UDF to check Interior.ColorIndex of cell and
return the name of the color.

Purpose is to allow the sales people, who like to indicate sales
progress on their leads with colors, to sort/filter on color to get an
overview.

Problem is that the sheet doesn't update after changed color, need to
go into cell with the function and Enter. F9 doesn't help.
Tried Application.Calculate(Full) but no.

Any ideas?

/Fredrik

Hello Fredrik,

It would help to the see your UDF code.

Tahnks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=378474

Fredrik,

As you have found, a change in a cell's colour does not trigger a sheet
recalculation, so your UDF doesn't fire.

What I usually do is to add a button, to the toolbar or the worksheet, that
shows the colour palette dropdown and when a colour is selected, does a
sheet calculate, and make the UDF volatile. Something like

Sub PickColour()
Dim rngCurr As Range
Application.Dialogs(xlDialogPatterns).Show
ActiveSheet.Calculate
End Sub

--
HTH

Bob Phillips

"FredrikLyhagen" wrote in message
oups.com...
Hi,

I have written a little UDF to check Interior.ColorIndex of cell and
return the name of the color.

Purpose is to allow the sales people, who like to indicate sales
progress on their leads with colors, to sort/filter on color to get an
overview.

Problem is that the sheet doesn't update after changed color, need to
go into cell with the function and Enter. F9 doesn't help.
Tried Application.Calculate(Full) but no.

Any ideas?

/Fredrik

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

Hi FredrikLyhagen

As you might have noted from Bob's post, it is not possible to achieve
by your method. You'll have to incorporate the code provided by Bob to
pick colors.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378474

Hi Fredrik,

One way to update such a udf might be to replace the formula in all cells
that contain the udf, eg

Sub UpDateUDF()
Dim sFirst As String
Dim cel As Range

On Error Resume Next
With ActiveSheet.UsedRange
Set cel = .Find(What:="FillColor", After:=.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False) _

If Not cel Is Nothing Then
cel.Formula = cel.Formula
sFirst = cel.Address
Do
Set cel = .FindNext(cel)
cel.Formula = cel.Formula
Loop While Not cel Is Nothing And cel.Address < sFirst
End If
End With

End Sub

In passing, couple of comments about your UDF. It's generally recommended
not to use a keyword like "Cell" as a variable name. A udf can only return a
value, so "Application.Calculate" or anything else that tries to change the
app or workbook does nothing.
Might be worth including an error handler:
On Error GoTo errH
'code
Exit Function
errH:
FillColor = CVErr(xlErrref)
End function

Bob - another of those early morning colour questions :-)

Regards,
Peter T


"FredrikLyhagen" wrote in message
oups.com...
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

See my earlier response.

--
HTH

Bob Phillips

"FredrikLyhagen" wrote in message
oups.com...
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

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

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

OH YES it does.

user still has to trigger a recalc but doesnt need to reenter the
formula or do a full rebuild.

normally a function will recalc only if the value of the referenced
cell changes AND a recalc is triggered (either by user or when
calculation is automatic.

VOLATILE functions will recalc REGARDLESS of the referenced cell
on ANY and ALL changes to the calculation tree.

THUS it will respond when user presses recalc.

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


Bob Phillips wrote :

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

"keepITcool" wrote in message
ft.com...
THUS it will respond when user presses recalc.

Exactly, so it doesn't change anything when a colour is changed.

we're both being stubborn <g

please try it out.

Just change a cell's color and press recalc.

without Volatile the udf will not recalculate.
with Volatile it will.

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


Bob Phillips wrote :


"keepITcool" wrote in message
ft.com...
THUS it will respond when user presses recalc.

Exactly, so it doesn't change anything when a colour is changed.

pls read my comments.
as you can see I disagree with Bob,

you'll still have to trigger a recalc,
but with application.volatile it does work.

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


mangesh_yadav wrote :


Hi FredrikLyhagen

As you might have noted from Bob's post, it is not possible to achieve
by your method. You'll have to incorporate the code provided by Bob to
pick colors.

Mangesh

Maybe we're just not communicating.

I know how Volatile works, I suggested it in my first response. The point I
made is that changing a colour will not update any formula. You have to
somehow force the recalc.

My point, maybe not well made, is that saying press recalc is not really
good enough on a spreadsheet, we should look for some other automated
method, or don't use colour in formulae.

Bob

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

we're both being stubborn <g

please try it out.

Just change a cell's color and press recalc.

without Volatile the udf will not recalculate.
with Volatile it will.

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


Bob Phillips wrote :


"keepITcool" wrote in message
ft.com...
THUS it will respond when user presses recalc.

Exactly, so it doesn't change anything when a colour is changed.

aha... I see and I concur :)

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


Bob Phillips wrote :

My point, maybe not well made, is that saying press recalc is not
really good enough on a spreadsheet, we should look for some other
automated method, or don't use colour in formulae.

It's all about design Jurgen (which is nothing new to you I know), but my
experience is that users just want to be in and out. Needing them to do say
a recalc is alien to them, so they probably won't. IMO we either sort it for
them or we don't use it. I gave such a method in my first post to the OP.

I often give suggestions on say counting by colour and recently I was
(gently) chided by Biff (at least I thin it was he), suggesting that these
are not good solutions. I some ways you have to agree.

Regards

Bob

"keepITcool" wrote in message
ft.com...
aha... I see and I concur :)

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


Bob Phillips wrote :

My point, maybe not well made, is that saying press recalc is not
really good enough on a spreadsheet, we should look for some other
automated method, or don't use colour in formulae.

Hi bob..

Normally I refuse to do "calculation with colors".

I can easily convince my clients it's NOT a good idea.
and it should work the other way. Much safer to store a value and
display a color. You just need a simple UI to enter those values


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


Bob Phillips wrote :

It's all about design Jurgen (which is nothing new to you I know),
but my experience is that users just want to be in and out. Needing
them to do say a recalc is alien to them, so they probably won't. IMO
we either sort it for them or we don't use it. I gave such a method
in my first post to the OP.

I often give suggestions on say counting by colour and recently I was
(gently) chided by Biff (at least I thin it was he), suggesting that
these are not good solutions. I some ways you have to agree.

Regards

Bob

"keepITcool" wrote in message
ft.com...
aha... I see and I concur :)

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


Bob Phillips wrote :

My point, maybe not well made, is that saying press recalc is not
really good enough on a spreadsheet, we should look for some other
automated method, or don't use colour in formulae.