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

<colgroup><col span="3"><col><col><col></colgroup><tbody></tbody>

A	B	C	Min	Max	Colored cells
0,91	0,97	1,21	0,36	7,85	0,98
1,18	0,89	1,00
1,23	1,00	0,87
0,92	0,91	1,21
0,81	1,24	1,67
0,87	1,13	1,33
1,02	0,87	1,12
0,92	1,11	1,50

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[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.