Color function issue

[Jayadev Sarangi]

Hello, I have this below code. but everytime there is a change in color in the range, I have to press F2 then press enter to recalculate teh formula. Can you please guide me in fixing that issue so that whenever there is a change in color the formula recalculates automatically and I do not have press F2 the press enter. Code -

[pre]

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

[/pre]

 

[Hui]

Adding a line

Application.Volatile True

Will make the function volatile,


But Volatile events aren't triggered by cells changing colors

 

[Jayadev Sarangi]

Thanks for the quick reply but I need the function to calculate automatically when cell color changes in the range.


Regards

Jay

 

[Hui]

Jay


You can use a technique that looks at the value of the changed cell

Undoes, the last change and looks at the old color

Compares them

Acts on the results

Resets the cells color


If you post a sample file I can add that for you

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


or refer you to:

(a file)

where the technique is used

 

[Jayadev Sarangi]

Thanks Hui, but putting the color is a judgemental call( manual effort ) and the number may not change everytime, but color changes.


Regards

Jay

 

[NARAYANK991]

Hi Jay ,


You can use a Worksheet_SelectionChange event procedure to force a recalculate , provided your worksheet is not otherwise compute-intensive.


Narayan

 

[Jayadev Sarangi]

Thanks. How can I do that? Please guide.


Regards

Jay

 

[Hui]

Problem is that the selection may not change and hence the event won't be triggered


No it isn't triggered

 

[Hui]

Can you ask them to simply press "Enter" after they change the color?

Then you can use the Workbook_SheetChange event to pick it up and act on it

 

[NARAYANK991]

Hi Jay ,


All you need to do is use the Calculate statement in either of the following two event procedures :


Worksheet_SelectionChange


Workbook_SheetChange


The first can be used if formulae referring to the ColorFunction function are used only in one worksheet ; the second can be used if formulae referring to the function are used in several worksheets.


Either way , all that the event procedure needs to have is the Calculate statement ; you can look up the Excel help on this to decide whether you need Application.Calculate or Worksheet.Calculate or Range.Calculate


This will be triggered when ever the cursor is moved from one cell to another , either by pressing the Enter key , or by using any of the cursor movement keys , or by using the mouse.


Narayan