VBA help...

[Jayadev Sarangi]

Hello, Please let me know if there is anything wrong in the below code. Because after updating the file I have to press F2 to get the desired result. It does not update automatically which I want. Kindly advise.


Regards

Jay

-----------------Code-------------

Function Colorfunction(rColor As Range, rRange As Range, Optional SUM As Boolean)


Dim rCell As Range


Dim lCol As Long


Dim vResult


lCol = rColor.Interior.ColorIndex


If SUM = True Then


For Each rCell In rRange


If rCell.Interior.ColorIndex = lCol Then


vResult = WorksheetFunction.SUM(rCell, vResult)


End If


Next rCell


Else


For Each rCell In rRange


If rCell.Interior.ColorIndex = lCol Then


vResult = 1 + vResult


End If


Next rCell


End If


Colorfunction = vResult


End Function

 

[NARAYANK991]

Hi Jay ,


I ran the code , and it works properly.


Narayan

 

[Luke M]

When you say you're updating the file, does that involve changing the numbers, or just coloring some cells? If the former, we may be able to help by adding the line

Application.Volatile

to the beginning of code. If it's the latter, we're stuck, since simply changing the format of cells doesn't trigger an event or a recalculation. =/

 

[Jayadev Sarangi]

I do both. updating the numbers and changing the color also. but when I copy paste the format from below cell or press F2 then the formula updates.


Any solution?


Regards

Jay

 

[Debraj]

Hi Jayadev,


It will be helpfull for us.. if you provide us the Complete Background Information for the query..

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

http://www.excelforum.com/excel-formulas-and-functions/456045-sum-by-fill-color-2.html

http://www.teachexcel.com/excel-help/excel-how-to.php?i=318095

http://www.brainbell.com/tutorials/ms-office/excel/Count_Or_Sum_Cells_That_Have_A_Specified_Fill_Color.htm


and finally..

Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9


Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

from PARENT Original Post..


Regards,

Deb

 

[Luke M]

If there is a solution, I can't think of a good one. =/

Is it possible to live with a delay? Maybe add a button or something that forces a recalc and verifies then that the data has been "updated"?


EDIT: Thanks Deb, for finding the background, (would have) saved me a bunch of time. =P