How to give function by cell color to sum two value in Excel
 

A B
1 x 3
2 y 2
3 z 4

If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
How this function can be made in excel with the property of cell?

How to give function by cell color to sum two value in Excel
 

You need VBA

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"viraj" wrote in message
...
A B
1 x 3
2 y 2
3 z 4

If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
How this function can be made in excel with the property of cell?

How to give function by cell color to sum two value in Excel
 

I tried the function =SUMPRODUCT(--(ColorIndex(A1:A100)=3),A1:A100) to sum
colored cell but when i enter this funtion it gives value as #NAME? kindly
help to solve

Regards



"Bob Phillips" wrote:

You need VBA

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"viraj" wrote in message
...
A B
1 x 3
2 y 2
3 z 4

If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
How this function can be made in excel with the property of cell?

How to give function by cell color to sum two value in Excel
 

THANK YOU IT WORKS

I copied the code and pasted in VBA module and the the function works. But
the value is not updateing automaticlly as excel does. every time i have to
double click the function to update the value. How to make the value change
when i change the color. Please Ref. below

A
1 100 (Red)
2 200
3 300 (Red)

Sum (400)

A
1 100 (Red)
2 200 (Red)
3 300

Sum (400) - this is not updateing automatically i have to double click then
this value is changing

Regards

How to give function by cell color to sum two value in Excel
 

You will not get it to update automatically on change of a cell colour,
because that action does not trigger a worksheet recalculation, so the UDF
cannot automatically be fired. You could add

Application.Volatile

at the start of the function, and then use Alt-F9 to force a recalc when you
change a colour.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"viraj" wrote in message
...
THANK YOU IT WORKS

I copied the code and pasted in VBA module and the the function works. But
the value is not updateing automaticlly as excel does. every time i have
to
double click the function to update the value. How to make the value
change
when i change the color. Please Ref. below

A
1 100 (Red)
2 200
3 300 (Red)

Sum (400)

A
1 100 (Red)
2 200 (Red)
3 300

Sum (400) - this is not updateing automatically i have to double click
then
this value is changing

Regards