# ColorFunction, modified

#### neil.crawford

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

Can someone tell me what the -2 does in the formula above?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
colorfunction is a user defined function. The -2 could be doing anything. You need to post the source code to the function.

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."

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.

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

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?

neil.crawford2.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid: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>

Replies
6
Views
381
Replies
18
Views
277
Replies
3
Views
139
Replies
5
Views
329
Replies
5
Views
189

1,220,966
Messages
6,157,125
Members
451,399
Latest member
alchavar

### 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?

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