Count the number of red background cells there are in a range.

If I had a range called "MyRangeToCountRedCells" or I guess it could be done on the active selection?  How would that look in VBA? assuming I want to store the result in a variable to use later in the procedure?
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like:

Function getCellCount(Rng As Range, BackgroundColor As Integer) As Integer
    Dim c As Range, cnt As Integer
    For Each c In Rng.Cells
        If c.Interior.Color = BackgroundColor Then cnt = cnt + 1
    Next
    getCellCount = cnt
End Function

Open in new window


call it like this via VBA:

getCellCount(range("MyRangeToCountRedCells"), vbred)

Open in new window


in Excel formula, use:
=getCellCount(MyRangeToCountRedCells,255)

Open in new window

RWayneHAuthor Commented:
Compile error, Syntax error in the "call it like this via vba"

getCellCount(range("MyRangeToCountRedCells"), vbred)  tried placing a named range out there of MyRangeToCountRedCells  

Is there a way to do it on the active selection?  and give the result a variable name?  something right right
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you upload a sample file to illustrate the output you needed?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

RWayneHAuthor Commented:
Sample file?  No sample file should be needed...  any sheet, change the background of some cells red, select them plus a few non red cells.  Run sub and get a number.  Not rocket science here.  I don't want to make this confusing.  Can use a function or not... does not matter to me.  Is there something that does not make sense in this request?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in VBA, we are using Selection to refer to selected cells, like:

getCellCount(Selection, vbRed)

Open in new window

RWayneHAuthor Commented:
when I put: getCellCount(Selection, vbRed)  in a sub it turns red.  Maybe I just do not know how to use/call a function then.  This does not work.

Function getCellCount(Rng As Range, BackgroundColor As Integer) As Integer
    Dim c As Range, cnt As Integer
    For Each c In Rng.Cells
        If c.Interior.Color = BackgroundColor Then cnt = cnt + 1
    Next
    getCellCount = cnt
End Function

Sub TestRedCellCount()

getCellCount(Selection, vbRed)

End Sub

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
when I put: getCellCount(Selection, vbRed)  in a sub it turns red
change:
getCellCount(Selection, vbRed)

Open in new window

to:
getCellCount Selection, vbRed

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
to complete the test:

Sub Button1_Click()
    Call TestRedCellCount
End Sub

Function getCellCount(Rng As Range, BackgroundColor As Integer) As Integer
    Dim c As Range, cnt As Integer
    For Each c In Rng.Cells
        If c.Interior.Color = BackgroundColor Then cnt = cnt + 1
    Next
    getCellCount = cnt
End Function

Sub TestRedCellCount()
    MsgBox "You got " & getCellCount(Selection, vbRed) & " cells within the selected range which is in Red color"
End Sub

Open in new window

29073917.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Ok, it runs now... but were is the number stored?  Can you add a msgbox for the result?  When debugging this the number result shows in the Local window, but once it breaks out to the function, the number disappears...
RWayneHAuthor Commented:
Sorry did not see your last post... reviewing it now.
RWayneHAuthor Commented:
That is it, however I had to chg the Sub too:

Sub TestRedCellCount()
  
    If getCellCount(Selection, vbRed) < 3 Then
        MsgBox ("These was less than 3 red cells")
    Else
        MsgBox ("There are 3 or more red cells")
    End If

End Sub

Open in new window

RWayneHAuthor Commented:
Thanks for your help with this...
RWayneHAuthor Commented:
I did not need the button but this is what worked for me...

Function getCellCount(Rng As Range, BackgroundColor As Integer) As Integer
    Dim c As Range, cnt As Integer
    For Each c In Rng.Cells
        If c.Interior.Color = BackgroundColor Then cnt = cnt + 1
    Next
    getCellCount = cnt

End Function

Sub TestRedCellCount()
  
    If getCellCount(Selection, vbRed) < 3 Then
        MsgBox ("These was less than 3 red cells")
    Else
        MsgBox ("There are 3 or more red cells")
    End If

End Sub

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Cooool, glad it helps
RWayneHAuthor Commented:
One last thing.  How do I pull the result out of the MsgBox? and save it as a variable to use later in the procedure?  I need to get rid of the hard stop that the msgbox creates.

MsgBox "You got " & getCellCount(Selection, vbRed) & " cells within the selected range which is in Red color"

I tried Result = getCellCount(Selection, vbRed)

getCellCount(Selection, vbRed) = Result

getCellCount Selection, vbRed = Result
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
How do I pull the result out of the MsgBox? and save it as a variable to use later in the procedure?

try something like this:

in Module:
Function TestRedCellCount() As String
    TestRedCellCount = "You got " & getCellCount(Selection, vbRed) & " cells within the selected range which is in Red color"
End Function

Open in new window


call this function when it's needed.

if you want to store it somewhere first, just declare a public variable to hold the value and refer to it when necessary.
RWayneHAuthor Commented:
Excellent!!!  Thanks again for the help.  -R-
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.