Smoke0101
08-08-2013, 08:09 AM
Hi guys, I am new to VBA and looking for a little help with what I think is a loop issue
So I am trying to create a VBA macro to run through and count the number of cells that contain a certain text (in this case Weapon), and also have the color green in them. My problem is that it doesnt like me and gives me an error.
So I am wondering if someone who is a little more experiences might have a better Idea. Thanks!
The Function is called ColorCount and passes a cell range, text, and then a single cell. The code is below the picture.
10393
Function ColorCount(rRange As Range, Asignee As String, cCol As Range)
Dim AsigTr As Double
Dim WhatColor As Double
Dim tempRes As Double
Dim rCell As Range
Dim Marker As Long
Marker = cCol.Interior.ColorIndex
tempRes = 0
For Each rCell In rRange
AsigTr = WorksheetFunction.Search(Asignee, rCell)
If AsigTr = 1 Then
WhatColor = rCell.Interior.ColorIndex
If WhatColor = Marker Then
tempRes = 1 + tempRes
End If
End If
Next rCell
ColorCount = tempRes
End Function
So I am trying to create a VBA macro to run through and count the number of cells that contain a certain text (in this case Weapon), and also have the color green in them. My problem is that it doesnt like me and gives me an error.
So I am wondering if someone who is a little more experiences might have a better Idea. Thanks!
The Function is called ColorCount and passes a cell range, text, and then a single cell. The code is below the picture.
10393
Function ColorCount(rRange As Range, Asignee As String, cCol As Range)
Dim AsigTr As Double
Dim WhatColor As Double
Dim tempRes As Double
Dim rCell As Range
Dim Marker As Long
Marker = cCol.Interior.ColorIndex
tempRes = 0
For Each rCell In rRange
AsigTr = WorksheetFunction.Search(Asignee, rCell)
If AsigTr = 1 Then
WhatColor = rCell.Interior.ColorIndex
If WhatColor = Marker Then
tempRes = 1 + tempRes
End If
End If
Next rCell
ColorCount = tempRes
End Function