Lesson#179: Count cells by color in Excel from a reference cell color with VBA
Here I shall make a VBA code to Count cells by color in Excel from a reference cell color.
- Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
- Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.
Here is a function in VBA that you can use to count the number of cells in a specified range that have the same interior color as a reference cell:
Function CountMatchingInteriorColors(referenceCell As Range, targetRange As Range) As Long
' Get the interior color of the reference cell
Dim referenceColor As Long
referenceColor = referenceCell.Interior.Color
' Initialize a counter to zero
Dim count As Long
count = 0
' Loop through the cells in the target range
Dim cell As Range
For Each cell In targetRange
' If the cell's interior color matches the reference color, increment the counter
If cell.Interior.Color = referenceColor Then
count = count + 1
End If
Next cell
' Return the count
CountMatchingInteriorColors = count
End Function
To use this function,
= CountMatchingInteriorColors(referenceCell, targetRange)
you can call it from your VBA code and pass it a reference cell and a target range as arguments. For example:
Leave a Reply