COUNTIF but if Hi-Lited Don't

What I am trying to do use a statement similar to =COUNTIF(A1:A10,"X") and it will count the x's in the column. What I want to do is if the row is hi-lited I want to ignore the count, so it the count is 5 and one row is hi-lited that has an X then I want the result to be 4.

Thanks, Dave

Originally Posted by Tony Valko

Is the row highlighted using conditional formatting? If so, you should be able to write a formula based on WHY the row is highlighted to do what you want.

The row is being highlighted manually and the color is random but for the sake of getting this to work the color can be specific.

Originally Posted by FDibbins

Hi and welcome to the forum

excel cant "see" colors. what you will need to do is determine the criteria to cause the row to be high-lighted in the 1st place, and then build that criteria into your countif()...although you will probably need to use countifS() instead

The row is high-lighted manually depending on the situation. I'll look into using countifs, that is a new one for me.

Originally Posted by Tony Valko

As FDibbins noted, Excel can't "see" color when calculating a formula.

When you change a cell format (color) that action does not trigger a calculation so a formula based on a cells color will not update until some event causes a calculation (or if you force a manual calculation).

For that reason I advise against basing formulas on cell formats (color).

If you insist on doing it that way then you'll need a VBA procedure. See this:

http://www.cpearson.com/Excel/colors.aspx

I'll read it over but I'm not a VBA guy but it may turn me into one. Thanks for steering me in a direction.

If I succeed I'll post the findings/results

The following VBA does exactly what I want:

Public Function Ccolor(rng As Range, reference As Variant) As Integer
Application.Volatile
Dim cell As Range
Dim val As Integer
val = 0
For Each cell In rng
If cell.Interior.Color <> vbYellow And cell.Value = reference Then
val = val + 1
End If
Next cell
Ccolor = val
End Function

So instead of Using COUNTIF use Ccolor and it will work with a yellow highlight.

Thank you,

Maud