ColorFunction, modified

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
colorfunction is a user defined function. The -2 could be doing anything. You need to post the source code to the function.
 

neil.crawford

New Member
Joined
Feb 15, 2006
Messages
17
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."
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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.:)
 
L

Legacy 98055

Guest

ADVERTISEMENT

Referring to this post. Somehow I lost track of it... Am sure one of you two can improve it. :)
 

neil.crawford

New Member
Joined
Feb 15, 2006
Messages
17
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?
 
L

Legacy 98055

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

Forum statistics

Threads
1,141,924
Messages
5,709,378
Members
421,633
Latest member
Ubergribbler

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
Top