Calculating 4 threads and colorindex - help with formula

[nonabc86]

Dear all,

I have a table for the production plan and I need help with two formulas.

First, I have macro about the color index, and when I change color in the tab "Plan proizvodnje.." from green (colorindex 43) to no fill (colorindex -4143) it won't change value automatically in column X. I have to double click on cell and press enter. Can someone please tell me what should I change in the module below so it changes value automatically?

1.
Function SumByColor(CellColor As Range, rRange As Range)
'Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

2.
Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

And the second problem is in the formula in tab "Stanje sirovina i ambalaze" in column C:
=SUMPRODUCT(SUMIF(INDIRECT("'"&RefSheet!$A$1:$A$24&"'!$A$4:$A$355");$A3;INDIRECT("'"&RefSheet!$A$1:$A$24&"'!$W$4:$W$355")))

With this formula, it needs a lot of time to calculate the values (it shows calculating 4 threads). Can I use some other formula to pick up all data from all sheets from "Plan proizvodnje jul to novembar" by dates?

Thank you in advance.

[JeteMc]

Hello nonabc86 and Welcome to Excel Forum.
As to the formula it may speed things up to list the sheets across the top of the "Stanje sirovina i ambalaze" sheet and use the following to get values per date from each sheet:

Please Login or Register  to view this content.

The sum of all sheets is then displayed in column using: =SUM(F3:O3)
You may also want to check under File > Options > Advanced > Formulas > make sure "Use all processors on this computer" is selected.
(Note that I don't know enough about VBA to help with the macro)
Let us know if you have any questions.