UDF for colors in a cell range

[saltysean]

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

 

[ttdk1]

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]

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]

sorry you want is still unclear

 

[mikerickson]

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]

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!