vba - Excel Countifs formula with one color criteria and the rest normal -

how excel 2010 formula can countifs criteria includes color? saw this: https://support.microsoft.com/en-us/kb/2815384 gives me count of cells color. need additional criteria available in formula or need vba provided in link there work on visible cells (using filters on data). ideas? appreciate help.

here code provided link:

function countccolor(range_data range, criteria range) long     dim datax range     dim xcolor long xcolor = criteria.interior.colorindex each datax in range_data     if datax.interior.colorindex = xcolor         countccolor = countccolor + 1     end if next datax end function 

andrew weatherly's answer you're looking for.

the thing had issues when running code line:

if datax.hidden = false

this becomes datax.entirerow.hidden = false , seems fix things.

it doesn't update when should.

in doing poking around, added application.volatile, doesn't seem update when color changes or when filter changes.

i forced add worksheet selectionchange event got updating when supposed (probably often).

excel doesn't have "color change" event, best can update when selection changes (short of adding timer runs application.calculate, sounds more effort it's worth).

the final code:

function countcolors(therange range, thecolor range) long application.volatile dim c, color, cellcount color = thecolor.interior.colorindex each c in therange     if c.entirerow.hidden = false , c.interior.colorindex = color cellcount = cellcount + 1 next c countcolors = cellcount end function 

sheet1 event:

private sub worksheet_selectionchange(byval target range) application.calculate end sub 

the first piece of code goes in module (module1 default).

the second piece of code goes in worksheet object (sheet1 (sheet1) in example).