[Solved] Count Cells in a row based on Color

[mfaisal.ce]

Dears,

I need to calculate number of cells in a row based on Color. Kindly guide me how to do. It is in a row not in a column .



A7= Count cells in row 7 where Color is Yellow

Regards,
Faisal

 

[Yousuf Shovon]

I need to calculate number of cells in a row based on Color. Kindly guide me how to do. It is in a row not in a column .

Hello Faisal,

To count cells in row 7 where the Fill Color is Yellow, you can define a custom function CountCellsByColor in VBA. The purpose of this function will be to count the number of cells in the specified range rng that have the same fill color as the cell referenced in color. Also, to make sure that the function recalculates whenever there is a change in the worksheet, use Application.Volatile. Follow the below steps to implement the VBA function:

• Copy the below VBA code to a module. Then, close the Visual Basic window.

Public Function CountCellsByColor(rng As Range, color As Range) As Long
    Dim count As Long
    Dim cell As Range
    Dim colorValue As Long

    Application.Volatile

    colorValue = color.Interior.color

    For Each cell In rng
        If cell.Interior.color = colorValue Then
            count = count + 1
        End If
    Next cell

    CountCellsByColor = count
End Function

• Now, enter the below VBA function in A7 >> press Enter key.

=CountCellsByColor(7:7,C7)

• Here, 7:7 indicates the whole row 7 and C7 denotes the interior cell color to count. In this case, C7 color is Yellow.

​

• Consequently, we obtain 2 as the total number of yellow-colored cells in row 7. You can add more colored cells and the function will adjust automatically.

​

Tips: Go to Change Cell Color Based on Text, to highlight your important data quickly and make your workbook more dynamic.

I am attaching the workbook. Try the code and let me know if it works for you.

Regards.

 

[mfaisal.ce]

Thanks Dear