Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating function

Status
Not open for further replies.

wmbb

Technical User
Jul 17, 2005
320
NL
I defined a function in excel called "totaal" counting the cells with a specific color.
Code:
Function totaal(rng As Range) As Integer
Application.Volatile
totaal = 0
For Each Cell In rng
     If Cell.Interior.color = 5287936 Then
     totaal = totaal + 1
     End If
Next

End Function

In several cells I entered a formula like "=totaal(B13:Z13)" with varying ranges.

How do I update the cells with the function on changing the sheet.
 


Hi,

If Calculation is set to AUTOMATIC it will calculate when a change is made.

Otherwise, you could use the worksheet_change event like this...
Code:
   intersect(target.entirerow, activesheet.usedrange).calculate
to calculate one row.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
The calculation is set to automatic but that doesn't work.

I did find out that after entering (or deleting) a value in a cell the function is recalculated.
But when I only change the color of a cell(interior) the function is not recalculated.
Is there an option the recalculate the function after changing the format of a cell ?
 
Use the Worksheet_SelectionChange event procedure ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Sorry I'm not an expert...
What is the command to use for the recalculation within the Worksheet_SelectionChange procedure ?
 
I think PHV was suggesting that you should put the line:

Application.Calculate

in the Worksheet_SelectionChange procedure.

That will cause the workbook to recalculate every time you change the selected cell. If your workbook calculates quickly, then that will not be a problem. But if that would make it too slow, you could first check if the selected cell was one of those whose colour you were interested in.

To do that, you would need to use the Target argument of the Worksheet_SelectionChange procedure. When you change your selection in the worksheet, the Worksheet_SelectionChange procedure is fired and the Target argument is a reference to the currently selected range. The .Row and .Column properties of the Target tell you where its top left corner is. The .Rows.Count and .Columns.Count properties tell you how high and how wide it is.

So, you could use that information to check if the selected range covered some of your coloured cells, and only cause the application to calculate if it did.

I hope that helps.

Tony
 
Okay I understand.
This is exactly what I had in mind.

Thank you all for your help !
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top