Remember Me?

#### Menu

#1
April 27th 06, 09:51 AM posted to microsoft.public.excel.worksheet.functions
 viraj Posts: n/a
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?

#2
April 27th 06, 10:01 AM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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
news
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?

#3
April 28th 06, 05:11 AM posted to microsoft.public.excel.worksheet.functions
 viraj Posts: n/a
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
news
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?

#4
April 28th 06, 07:45 AM posted to microsoft.public.excel.worksheet.functions
 viraj Posts: n/a
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

#5
April 28th 06, 09:59 AM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM Matthew Excel Discussion (Misc queries) 1 December 7th 05 01:10 AM Joseph Excel Discussion (Misc queries) 2 June 3rd 05 11:59 AM Tony Excel Worksheet Functions 5 November 29th 04 11:52 PM Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM

All times are GMT +1. The time now is 01:21 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017