function "count if color"
 

Hello, I have a function that counts (or sums) all the cells in a range that
have the same color than the sample cell
CountIfColor (Range,SampleCell)
I use a For-Next loop, and I compare the color of every cell with the color
of the sample cell.

The problem comes when I change the color of one of the cells of the range:
the function doesn't recalculates. If I change the value goes ok, but if I
change the color not.

I can't use Worksheet_Change because the function is located in an .xla
sheet, and the users do not know how to work with Vb.

Can someone give me an idea (maybe a different focus) to help me?

Thanks in advance

function "count if color"
 

Try to add this code at the beginning of your function:

Application.Volatile

it forces Excel to recalculate function every time you change anything on
the spreadsheet.

Best -
RADO


"José Ignacio Bella" wrote in message
...
Hello, I have a function that counts (or sums) all the cells in a range
that
have the same color than the sample cell
CountIfColor (Range,SampleCell)
I use a For-Next loop, and I compare the color of every cell with the
color
of the sample cell.

The problem comes when I change the color of one of the cells of the
range:
the function doesn't recalculates. If I change the value goes ok, but if I
change the color not.

I can't use Worksheet_Change because the function is located in an .xla
sheet, and the users do not know how to work with Vb.

Can someone give me an idea (maybe a different focus) to help me?

Thanks in advance

function "count if color"
 

If the function reevaluates when you change a force a recalculation by changing
something not in your range, then you already have application.volatile in the
code.

But excel doesn't recalc when you change colors of fonts or fills.

Maybe an F9 (tools|option|calculation tab|Calculate now) will be sufficient.

This is from xl2002's help:

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

(I think that the last one is new with xl2002.)



"José Ignacio Bella" wrote:

Hello, I have a function that counts (or sums) all the cells in a range that
have the same color than the sample cell
CountIfColor (Range,SampleCell)
I use a For-Next loop, and I compare the color of every cell with the color
of the sample cell.

The problem comes when I change the color of one of the cells of the range:
the function doesn't recalculates. If I change the value goes ok, but if I
change the color not.

I can't use Worksheet_Change because the function is located in an .xla
sheet, and the users do not know how to work with Vb.

Can someone give me an idea (maybe a different focus) to help me?

Thanks in advance

--

Dave Peterson