count cell with background color 36

  • Thread starter Thread starter oldjay
  • Start date Start date
O

oldjay

First I want to count the number of cells in a range that have text in them.
Then I want to count how many of these cells have the back ground color 36
 
Oldjay

This will achieve what you want for a continuous range, Column A in
this example. Change the variables to suit.

Take care

Marcus

Option Explicit
Sub MyCount()
Dim lw As Integer
Dim counter As Integer
Dim MyConstant As Integer
Dim i As Integer

lw = Range("A" & Rows.Count).End(xlUp).Row
counter = 0
MyConstant = Range("A2" & lw).SpecialCells(xlCellTypeConstants).Count
MsgBox "There are " & MyConstant & " Cells with constants in the Range
"

For i = 1 To lw
If Range("A" & i).Interior.ColorIndex = 36 Then
counter = counter + 1
End If
Next
MsgBox counter
End Sub
 
I should have been more explicit in my request. First I want to count the
number of cells in a range (A1:F30) that have text in them (there will only
be text or blank). Then I want to count all the cell that have a background
of color 36. I then want to export these results to cells H1 and H2.
 
Hi Oldjay

Sorry for the delay - weekend and well away from the computer. This
should do what you want. Enjoy your weekend.

Change the variables, such as sheet name to suit.

Take care

Marcus

Option Explicit
Sub MyCount()

Dim lw As Integer
Dim MyConstant As Integer
Dim Counter As Integer
Dim rRange As Range
Dim rCell As Range

Set rRange = Range("A1:F30")
lw = Range("A" & Rows.Count).End(xlUp).Row
MyConstant = Application.CountA(Sheets("Sheet1").Range("A1:F30"))
Range("H1").Value = MyConstant

For Each rCell In rRange
If rCell.Interior.ColorIndex = 36 Then
Counter = 1 + Counter
End If
Next rCell
Range("H2").Value = Counter

End Sub
 
Back
Top