Count if based on value and color vba

Question

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

how are your cells colored; Manually through formatting, or Automatic through conditional formatting? — Scott Craner
– Scott Craner, Commented Apr 12, 2018 at 16:24
it shows a value error at the begining. But now it works fine. Just it doesn't update automatically when I change the color of the cell — youssef mhiri
– youssef mhiri, Commented Apr 12, 2018 at 20:01

PaulCh · Accepted Answer · 2018-04-13 11:00:57Z

1

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.

answered Apr 13, 2018 at 11:00

PaulCh

263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count if based on value and color vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related