ColorFunction, modified

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
colorfunction is a user defined function. The -2 could be doing anything. You need to post the source code to the function.
 
Upvote 0
I like your quote.

This is my original problem description:

To count cells of a given color, I'm presently using this formula, =ColorFunction($L$2,Desktops!$D$3:$D$77).

I need to segregate the counts based on another column as well. For instance, "Count all the cells on the Desktop worksheet/Column D with the color found in L2, if the value in Column B of the same row is 4X."
 
Upvote 0
Neil

I can't see how we can help without seeing the source code for the function.

If I google for ColorFunction VBA I get 121 hits.

Now they may all be pointing to the same place, but I don't think they are.:)
 
Upvote 0
Extremely confused now

=colorfunction(L2,D:D,-2,"4X")

What does the -2 do in your formula?

Is there a purpose in hiding Column F?

It appears your formula sums the contents of D:D. I need it to count the cells of D:D.

I can't improve it. I don't even understand it. Tom, you wrote it didn't you?
 
Upvote 0
neil.crawford2.zip

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Function</font> CountColors(LookupColor <font color="#0000A0">As</font> Range, ColorLookupRange <font color="#0000A0">As</font> Range, _
       LookupValue, ValueLookupRange <font color="#0000A0">As</font> Range)

       <font color="#0000A0">Dim</font> c <font color="#0000A0">As</font> Range
       <font color="#0000A0">Dim</font> ColorIndex <font color="#0000A0">As</font> <font color="#0000A0">Variant</font>
       <font color="#0000A0">Dim</font> cIndex <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

       ColorIndex = LookupColor.Interior.ColorIndex

       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> c <font color="#0000A0">In</font> ValueLookupRange
           cIndex = cIndex + 1
           <font color="#0000A0">If</font> c.Value = LookupValue <font color="#0000A0">Then</font>
               <font color="#0000A0">If</font> ColorLookupRange(cIndex).Interior.ColorIndex = ColorIndex <font color="#0000A0">Then</font>
                   CountColors = CountColors + 1
               <font color="#0000A0">End</font> <font color="#0000A0">If</font>
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">Next</font> c

  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("922200615457906").value=document.all("922200615457906").value.replace(/<br \/>\s\s/g,"");document.all("922200615457906").value=document.all("922200615457906").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("922200615457906").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="922200615457906" wrap="virtual">
Function CountColors(LookupColor As Range, ColorLookupRange As Range, _
LookupValue, ValueLookupRange As Range)

Dim c As Range
Dim ColorIndex As Variant
Dim cIndex As Long

ColorIndex = LookupColor.Interior.ColorIndex

For Each c In ValueLookupRange
cIndex = cIndex + 1
If c.Value = LookupValue Then
If ColorLookupRange(cIndex).Interior.ColorIndex = ColorIndex Then
CountColors = CountColors + 1
End If
End If
Next c

End Function</textarea>
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top