PC Review


Reply
Thread Tools Rate Thread

count cell with background color 36

 
 
oldjay
Guest
Posts: n/a
 
      27th Nov 2009
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
 
Reply With Quote
 
 
 
 
marcus
Guest
Posts: n/a
 
      27th Nov 2009
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
 
Reply With Quote
 
FSt1
Guest
Posts: n/a
 
      27th Nov 2009
hi
you might also check this site out for color IDF's.
count, sum, sort, other.
http://cpearson.com/excel/colors.aspx

regards
FSt1

"oldjay" wrote:

> 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

 
Reply With Quote
 
oldjay
Guest
Posts: n/a
 
      28th Nov 2009
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.

"marcus" wrote:

> 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
> .
>

 
Reply With Quote
 
marcus
Guest
Posts: n/a
 
      28th Nov 2009
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
RE: Count number of cells with certain background color Mike H Microsoft Excel Programming 1 31st Jan 2009 04:44 PM
RE: Count number of cells with certain background color JLGWhiz Microsoft Excel Programming 0 31st Jan 2009 04:41 PM
Cell background color (interior color) setting not working Martin E. Microsoft Excel Programming 1 21st May 2006 07:00 PM
Default Border, Font Color, and Cell Background Color Elijah Microsoft Excel Misc 1 28th Oct 2005 04:10 PM
Need help with complicated use of count background color of Cell =?Utf-8?B?SmFuZQ==?= Microsoft Excel Programming 8 8th Aug 2005 04:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:10 AM.