Nou
 Comunitate

Se automatizează Sum by ColorFunction

  Excel
MrRemodel
  MrRemodel
  Joi, 05 Ianuarie 2023
  1 Răspunsuri
  3.6K vizite
0
Voturi
Anula
Thanks in advance for any help on this that you can offer.

With the help of this site I have created the following function;

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)
Final, dacă
Next rCell
Altfel
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
Final, dacă
Next rCell
Final, dacă
ColorFunction = vResult
Sfârşit Funcţia


In each target cell that I want a sum of all the of the cells in that row that contain the color of the specified cell;
=colorfunction(AR4,H5:AP5,TRUE)

How can I get the target sell to update when any other cell in the row (H5:AP5) changes color?
0
Cele mai vechi primele  
Amanda Lee
Amanda Lee
1 ani în urmă
·
#3338
0
Voturi
Anula
Salut,

You can add the code: Application.Calculation = xlAutomatic to your function:

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

Dim rCell As Range

Dim lCol As Long

Dim vResult

Application.Calculation = xlAutomatic

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


Vă rugăm să încercați.

Amanda
  • Pagina:
  • 1
Nu există răspunsuri făcute pentru acest post.