Hi,
see Chip explanation

Color Change And Calculation

Excel normally calculates the formula in a cell when a cell upon which that
formula depends changes. For example, the formula =SUM(A1:A10) is
recalculated when any cell in A1:A10 is changed. However, Excel does not
consider changing a cell's color to be significant to calculation, and
therefore will not necessarily recalculate a formula when a cell color is
changed. Later on this page, we will see a function named CountColor that
counts the number of cells in a range that have a specific color index. If
you change the color of a cell in the range that is passed to CountColor,
Excel will not recalculate the CountColor function and, therefore, the result
of CountColor may not agree with the actual colors on the worksheet until a
recalculation occurs. The relevant functions use Application.Volatile True to
force them to be recalculated when any calculation is done, but this is still
insufficient. Simply changing a cell color does not cause a calculation, so
the function is not recalculated, even with Application.Volatile True

if this helps please click yes thanks

"whatzzup" wrote:

Hi all, this is what i wish to have happen; i have created a list of
activities B5:B25 say, next to them i have two column Start & Finish D & E,
next to them i have columns G to J, where G1:J1 has date fields in cells 8th
Jan 10, 15th Jan 10, to 29th Jan 10 week between them. The question is how
can i retrurn a date in cell D5 if i populate G5 (or fill with a colour)?