CODEWITHSUNDEEP
vbaexcel
youssef mhiri
youssef mhiri

Reputation: 133

Count if based on value and color vba

I want to count number of cells with two criteria : their color and their value.

I select the range, then the color, then the value of the cell.

I made this code in vba but it doesn't work.

Function CountCcolor(range_data As Range, criteria As Range, cellvalue As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor And datax.value = cellvalue.value Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

I think that the problem is in my cellvalue.value I don't know how I can access a selected cell value.

Thank you for your help

Upvotes: 0

Views: 570

Answers (1)

PaulCh
PaulCh

Reputation: 26

First time trying to help someone on here, so I hope I'm doing it right!!

Try adding

Application.Volatile

to your code just below your variables declaration. This makes Excel recalculate your function when it performs its standard recalc.

However, if you use your UDF a lot, things can slow down. Also becomes a monumental pain when stepping through code, if you haven't disabled autocalc.

Hope this helps.

Upvotes: 1

Related Questions