Hi,
So I am in the process of creating a spreadsheet for a business venture and I am using the spreadsheet as an internal worksheet for services that we provide.
On worksheet 1 I have an extensive list of services we provide. Each service is connected to a checkbox. Worksheet 2 is our service packages (we have over 26 packages), and there is a lot of overlap between services and our packages and for a good reason.
The way I have it set up is through conditional formatting on Worksheet 2, I connected all the services to the checkboxes on worksheet 1. When a service is selected on worksheet one, the same service is cell colored 'green' and up to this point it works perfectly. The problem that I am having is the most important part, depending on the services the clients desire will determine which package makes the most sense for them. I figured the best way to do this is to count the colored cells within each package and the highest number is the obvious choice, making our package selection super easy.
I have tried countif() functions to no avail. I am currently working with VBA and have this code in module 1:
Function ColorCount(rColor As Range, rRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
ColorCount = vResult
End Function
I use =colorcount(range,reference color cell 'green') and it is not picking up the conditional formatting of my colored cells. I know that the problem is because of the conditional formatting because if i manually color cells this color count works perfectly. Just for additional info, the way that I did my conditional formatting is complicated. My services on worksheet 1 cannot be reference through conditional formatting, so I ended up specifically naming each cell that has a service i.e. cell A2 on worksheet 1 I named EWCA2 and cell A3... EWCA3 and so on. I then referenced those cells in conditional formatting and used cell color 'green'. I have spent hours on this and this is the first time using VBA, please help me whoever can.... thank you in advanced!
So I am in the process of creating a spreadsheet for a business venture and I am using the spreadsheet as an internal worksheet for services that we provide.
On worksheet 1 I have an extensive list of services we provide. Each service is connected to a checkbox. Worksheet 2 is our service packages (we have over 26 packages), and there is a lot of overlap between services and our packages and for a good reason.
The way I have it set up is through conditional formatting on Worksheet 2, I connected all the services to the checkboxes on worksheet 1. When a service is selected on worksheet one, the same service is cell colored 'green' and up to this point it works perfectly. The problem that I am having is the most important part, depending on the services the clients desire will determine which package makes the most sense for them. I figured the best way to do this is to count the colored cells within each package and the highest number is the obvious choice, making our package selection super easy.
I have tried countif() functions to no avail. I am currently working with VBA and have this code in module 1:
Function ColorCount(rColor As Range, rRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
ColorCount = vResult
End Function
I use =colorcount(range,reference color cell 'green') and it is not picking up the conditional formatting of my colored cells. I know that the problem is because of the conditional formatting because if i manually color cells this color count works perfectly. Just for additional info, the way that I did my conditional formatting is complicated. My services on worksheet 1 cannot be reference through conditional formatting, so I ended up specifically naming each cell that has a service i.e. cell A2 on worksheet 1 I named EWCA2 and cell A3... EWCA3 and so on. I then referenced those cells in conditional formatting and used cell color 'green'. I have spent hours on this and this is the first time using VBA, please help me whoever can.... thank you in advanced!
