Count cells with content in different color background

B

bribri

My question here is that I have a spreadsheet, with cells in different
background colors. However, not all cells have content:

For example: some cells in grey have numbers, some cells in grey are
empty with no input. So are cells in other colors.

For cells with each of the color backgrounds I set for them, I want to
count all the cells with content and all the cells without content.

Would any experts here provide a solution???

Best
bri
 
P

Paula Luxenberg

I don't know how many colors you have and if they need to be dynamic,
but here's a simple macro:

Sub FindColorCells()
Dim ColorRange As String, Cell As Variant, ColorNumber As Long,
BlankCells As Integer, NonBlankCells As Integer

ColorRange = "A1:A10" 'Your Range
ColorNumber = 65535 'Your color (This is yellow)

For Each Cell In Range(ColorRange)
If Cell.Interior.Color = ColorNumber Then
If Cell.Value = vbNullString Then
BlankCells = BlankCells + 1
Else
NonBlankCells = NonBlankCells + 1
End If

End If
Next Cell

'The results of your macro
MsgBox "There were " & BlankCells & " blank cells and " &
NonBlankCells & " non blank cells in your range"

End Sub
 
M

mp

Paula Luxenberg said:
I don't know how many colors you have and if they need to be dynamic,
but here's a simple macro:

Sub FindColorCells()
Dim ColorRange As String, Cell As Variant, ColorNumber As Long,
BlankCells As Integer, NonBlankCells As Integer

ColorRange = "A1:A10" 'Your Range
ColorNumber = 65535 'Your color (This is yellow)

For Each Cell In Range(ColorRange)
If Cell.Interior.Color = ColorNumber Then
If Cell.Value = vbNullString Then
BlankCells = BlankCells + 1
Else
NonBlankCells = NonBlankCells + 1
End If

End If
Next Cell

'The results of your macro
MsgBox "There were " & BlankCells & " blank cells and " &
NonBlankCells & " non blank cells in your range"

End Sub

and if you need to handle a cell that 'looks' blank but has a space(s) in
it...
If Len(Trim(Cell.Value))>0 then
....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top