Excel Formula with VBA

Question

Need formula to match text from one column to text in different worksheet and count the highlighted cells. This is similar to doing a sumif, but instead of returning a numerical value in a static column, I will return the count of highlighted cells.

I have successfully written the VBA to count the highlighted cells in a given column, but now must do a match of names. Meaning, if name in column A1:A50 matches name in Sheet2 Column J1:J52, then return a count of highlighted cells in sheet 2 column X.

Formula to count highlighted cells: countbycolor('sheet2'!J4:J1847,A52)

VBA:

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
    Dim cl As Range, TmpCount As Long, ColorIndex As Integer
    Application.Volatile
    ColorIndex = ColorRange.Interior.ColorIndex
    TmpCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Interior.ColorIndex = ColorIndex _
        Then TmpCount = TmpCount + 1
    Next cl
    CountByColor = TmpCount
End Function

A1:A50 contains 50 cells to check against J1:J52 which contains 52 cells. Are you looking to see if the text in A1 is contained anywhere in J1:J52, or are you wanting to check if A1 text is equal to J1 text? — shagans
mix of both, only because I could not come up with any other way to count a colored cell other than using VBA. If there is a way to do what I need (match name and count colored cell) and not use VBA then I will certainly give it a try. — ExcelQuestions
Honestly, I haven't tried Names with the backcolor but only with the font color as shown Here If you want, you can try and use it with This and see if it works? — Siddharth Rout

Unknown Unknown · Accepted Answer · 2015-05-12T20:35:53

Adding a parameter for the criteria range and implementing Application.Countif should be sufficient.

Function CountByColorAndName(InputRange As Range, NameRange As Range, ColorRange As Range) As Long
    Dim cl As Range, TmpCount As Long, ColorIndex As Integer
    Application.Volatile
    ColorIndex = ColorRange.Interior.ColorIndex
    TmpCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex and _
          cbool(application.countif(NameRange , cl.value)) then _
            TmpCount = TmpCount + 1
    Next cl
    CountByColor = TmpCount
End Function

Sample syntax:

=CountByColorAndName('sheet2'!J4:J1847, A1:A50, A52)

There is a little confusion as your description of the situation refers to both Sheet2 Column J1:J52 and 'sheet2'!J4:J1847. If this isn't appropriate, please clarify.

The MATCH function is actually more efficient than the COUNTIF function, both on the worksheet and within VBA. This should reduce calculation load some.

    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex then _
          if not iserror(application.match(cl.value, NameRange , 0)) then _
            TmpCount = TmpCount + 1
    Next cl

Excel Formula with VBA

1 Answers