Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 27th 06, 09:51 AM posted to microsoft.public.excel.worksheet.functions
viraj
 
Posts: n/a
Default 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   Report Post  
Old April 27th 06, 10:01 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Old April 28th 06, 05:11 AM posted to microsoft.public.excel.worksheet.functions
viraj
 
Posts: n/a
Default 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   Report Post  
Old April 28th 06, 07:45 AM posted to microsoft.public.excel.worksheet.functions
viraj
 
Posts: n/a
Default 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   Report Post  
Old April 28th 06, 09:59 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
How do i execute a VBA function by clicking on an excel cell? Matthew Excel Discussion (Misc queries) 1 December 7th 05 01:10 AM
how to give cell reference using Combo Boxes in Excel? Joseph Excel Discussion (Misc queries) 2 June 3rd 05 11:59 AM
Sum function not working correctly in Excel (Skips Cell) Tony Excel Worksheet Functions 5 November 29th 04 11:52 PM
GET.CELL 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