UDF for colors in a cell range

saltysean

New Member
Hi Guys

New member here stuck when using UDF for the first time. I am trying to write a function that reads the colours of an individual cell in a cell range and returns a value depending on what colour is highlighted. My Function looks like:

Function CheckColor2(range)
If range.Interior.ColorIndex = 3 Then
CheckColor2 = "Poor"
ElseIf range.Interior.ColorIndex = 44 Then
CheckColor2 = "Fair"
ElseIf range.Interior.ColorIndex = 6 Then
CheckColor2 = "Good"
ElseIf range.Interior.ColorIndex = 43 Then
CheckColor2 = "Excellent"
Else
CheckColor2 = "N/A"
End If
End Function

In the cells I enter:

=CheckColor2(C23:F23)

The issue I am having is that all the cells in my range need to be highlighted for the function to work. Is there a way to rewrite this so that if any cell within my range is a certain colour it will return the correct value?

Salt

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ttdk1

Board Regular
the problem with your UDF is that you want the function to return a value. If there are multiple values, which value should the function return? if one cell is poor, one is good, one is N/A, another is excellent… what's the correct one?

saltysean

New Member
the problem with your UDF is that you want the function to return a value. If there are multiple values, which value should the function return? if one cell is poor, one is good, one is N/A, another is excellent… what's the correct one?

I want the function to populate a summary table by pulling the data from the main body of the sheet. The sheet will be used to rate quality, so there will be four cells each with the words "Poor","Fair","Good","Excellect" and the grader will highlight this with the relevent colour. So they will only be entering one colour accross that range of four cells.

I then want the function to read that range and find out what has been entered and return a value in my summary table.

My ultimate goal to to reduce the duplication of work, just entering it in once only

ttdk1

Board Regular
sorry you want is still unclear

Last edited:

mikerickson

MrExcel MVP
Far far far far better than having the user color a cell would be to have them select Poor, Fair, Good or Excelent from a drop down and then have Conditional Formatting spread the color to where it needs to go.

Excel is not designed for color as input. Changing the color of a cell triggers neither calculation nor any event. Excel expects inputs to be the user changing cell values, not changing cell colors.

Your current scheme of user color > excel turns into text is precisely backwards from the way that Excel works. User entered text > Excel colors is much much easier and a better interface.

Besides, the training time is decreased. You don't have to tell the user that Red=Poor. (They already know that Poor = Poor.)

(Yes, its a bit of a pet peeve with me. )

saltysean

New Member
Far far far far better than having the user color a cell would be to have them select Poor, Fair, Good or Excelent from a drop down and then have Conditional Formatting spread the color to
where it needs to go.

Excel is not designed for color as input. Changing the color of a cell triggers neither calculation nor any event. Excel expects inputs to be the user changing cell values, not changing cell colors.

Your current scheme of user color > excel turns into text is precisely backwards from the way that Excel works. User entered text > Excel colors is much much easier and a better interface.

Besides, the training time is decreased. You don't have to tell the user that Red=Poor. (They already know that Poor = Poor.)

(Yes, its a bit of a pet peeve with me. )

Thanks Mike thats a way better way to do it!

Replies
2
Views
208
Replies
5
Views
151
Replies
0
Views
140
Replies
5
Views
749
Replies
3
Views
250

1,191,177
Messages
5,985,137
Members
439,941
Latest member
robertv13

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.

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

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