ColorFunction, modified

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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
75,919
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,114,096
Messages
5,545,926
Members
410,713
Latest member
TaremyLunsil
Top