Product sum for min, max and the colored cells in VBA

[bilbon]

Hi
Someone who can help me figure out the product sum for min, max and the colored cells in A2:C9.
The coloured cells have a colour index of 15 and 40.
Preferably VBA code

/Bilbon



[TABLE="width: 367"]
<colgroup><col span="3"><col><col><col></colgroup><tbody></tbody>[/TABLE]

[TABLE="width: 367"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Colored cells[/TD]
[/TR]
[TR]
[TD]0,91[/TD]
[TD]0,97[/TD]
[TD]1,21[/TD]
[TD]0,36[/TD]
[TD]7,85[/TD]
[TD]0,98[/TD]
[/TR]
[TR]
[TD]1,18[/TD]
[TD]0,89[/TD]
[TD]1,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1,23[/TD]
[TD]1,00[/TD]
[TD]0,87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0,92[/TD]
[TD]0,91[/TD]
[TD]1,21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0,81[/TD]
[TD]1,24[/TD]
[TD]1,67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0,87[/TD]
[TD]1,13[/TD]
[TD]1,33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1,02[/TD]
[TD]0,87[/TD]
[TD]1,12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0,92[/TD]
[TD]1,11[/TD]
[TD]1,50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[bilbon]

Hi DanteAmor


Min and Max work everywhere in the blade but UDF_Colored cells only in the columns A:C,
if I move the data to eg. H:J so it gives #VALUE ! Error for UDF_Colored cells.

/Bilbon

 

[DanteAmor]

Function udf_colored_cells(rng As Range)


[COLOR=#0000ff]    For i = 1 To rng.Rows.Count[/COLOR]
[COLOR=#0000ff]        For j = 1 To rng.Columns.Count[/COLOR]
            If rng.Cells(i, j).Interior.ColorIndex = 15 Or rng.Cells(i, j).Interior.ColorIndex = 40 Then
                If pCol = 0 Then pCol = rng.Cells(i, j).Value Else pCol = pCol * rng.Cells(i, j).Value
            End If
        Next
    Next
    udf_colored_cells = pCol
    
End Function

 

[bilbon]

Hi DanteAmor


Thank you for taking the time.
Now the code works perfectly.
Very grateful


/Bilbon

 

[DanteAmor]

You're welcome.