Formula for counting cells with Fill Color

[dean_mcwhorter]

Is there away to count cells with 1 or more different fill colors in a column?

Thanks, Dean

 

[mikerickson]

How about adding a helper column and if the grader double clicks on a cell in that column, "Out of Spec" fills the cell, the row turns yellow (via Conditional Formatting) and the formulas can look to the Out of Spec column for their data.

One unasked for benifit of this is that AutoFilter can isolate the Out of Spec items quickly and without having to write a macro.

 

[dean_mcwhorter]

That I like. Let me explain; The grader tests 100's of items and the do get an Avg and StDev but along with that he must grade the Visual attribute along with the test. If it fails you say we can use the conditional formatting that I do understand how to set the parameters on the Format Toolbar area........Dean

 

[dean_mcwhorter]

Mike,

Thanks for your help the last suggestion fits in well. I am going to go
play with that one.

Dean

 

[mikerickson]

If the Out of Spec column is column A then this Before Double Click event will do what you want. It should go in the Sheet's code module. (Double clicking again will remove the "Out of Spec")

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    With Target
        If .Column = 1 Then
            Cancel = True
            If .Value <> "Out of Spec" Then
                .Value = "Out of Spec"
            Else
                .Value = vbNullString
            End If
        End If
    End With
End Sub

The conditional formatting can be done by selecting A1:AZ1000 and setting conditional formatting to the formula
=($A1="Out of Spec")
with a yellow format.

 

[Simon Lloyd]

lateral thinking Mike....nice

 

[mikerickson]

I got to thinking, what if the user types into the cell, which lead to thougts of List Validation, which led to this Double Click routine.

With this installed, if a cell in column A that has explicit List Validation, (a,b,c not AA1:AA10) is double clicked upon, the cell entry will advance one item on the list.
Double click on "b" and "c" will be in the cell.
Double click on "c" will empty the cell.
DCing on that will result in "a".

It is an alternative to Red=West,Blue=North,... style color coding.
As written, this works with column A cells having any explicit list validation. The values in the list are not hard coded into VBA, but controlled from Excel.

This goes in the sheet's code module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Rem double click on a cell that has explicit list validation (a,b,c not A1:A10) and cylcle through
    Rem    the list, including blank.
    Dim actionRange As Range
    Dim listStrings As Variant
    Dim IndexInList As Long, countOfList As Long
    
    Set actionRange = Target.Parent.Range("A:A"): Rem adjust

    With Target
        If Not Application.Intersect(Target, actionRange) Is Nothing Then
            On Error Resume Next
            If TypeName(.Validation.Type) <> "Long" Then
                On Error GoTo 0
                Rem no validation = do nothing
            Else
                On Error GoTo 0
                
                With .Validation
                    If .Type = 3 And Not (.Formula1 Like "=*") Then
                        Rem if explicit list style validation then
                        Cancel = True
                        
                        Rem get list of validation values
                        listStrings = Split(.Formula1, ",")
                        countOfList = UBound(listStrings) + 2
                        
                        Rem add vbNullString to the list
                        ReDim Preserve listStrings(0 To UBound(listStrings) + 1)
                        listStrings(UBound(listStrings)) = vbNullString
                        
                        Rem advance one in the list
                        On Error Resume Next
                            IndexInList = (Application.Match(Target.Value, listStrings, 0) Mod countOfList)
                        On Error GoTo 0
                        Application.EnableEvents = False
                            Target.Value = listStrings(IndexInList)
                        Application.EnableEvents = True
                    End If
                End With
            End If
        End If
    End With
End Sub