Change cell value based on C.F. color of another cell

[DarrenK]

Hello,

In column L, I have a mathematical formula that displays scores from prior cells. Based on the score in column L, I have conditional formatting setup so it shades those values with any of 4 colors.
(255,0,0), (255,255,0), (146,208,80) & (0,176,80).
That works fine. However, in the adjacent M cells, I would like to have a quartile value displayed based on the color of the L cell next to it. Since the L cell is shaded only with C.F., the column M formula (below) appears to be recognizing the color in the L cells as uncolored.

My quartiles are setup as follows:
(255,0,0) = 4
(255,255,0) = 3
(146,208,80) = 2
(0,176,80) = 1

1) Can I have the M cell formula "see" the C.F. color and make it's determination based off of that without having to manually shade the adjacent cell?
2) Do I have the color codes in the formula below correct based on the RGB values listed above?

I have the formula below. If I enter =MyColor(L3) into M3, it displays a 0. I am guessing due to it seeing a non-colored cell in L3.

Function MyColor(myRange As Range) As Long

Dim myValue As Long

Select Case myRange.Interior.Color
'Red
Case 255
myValue = 4
'Yellow
Case 2552550
myValue = 3
'LtGrn
Case 14620880
myValue = 2
'DkGrn
Case 17680
myValue = 1
End Select

MyColor = myValue

End Function

 

[Fluff]

A UDF cannot "see" conditional formatting. Why not just use the CF rule to determine what value col M should be.

 

[DarrenK]

I'm not that familiar with C.F. to have it populate the cells. here's what I have (L3):

4 formulas, 1 per color:
=AND(L3>$S$6,L3<=$S$7) which shades cell Dark Green
=AND(L3>$S$5,L3<=$S$6) " " " Light Green
=AND(L3>$S$4,L3<=$S$5) " " " Yellow
=L3<=$S$4 and that shades the cell Red

How would I adjust the C.F. to have it display a 1 (dark green) through 4 (red)?

 

[Fluff]

Ok, how about in M3

=IF(AND(L3>$S$6,L3<=$S$7),1,IF(AND(L3>$S$5,L3<=$S$6),2,IF(AND(L3>$S$4,L3<=$S$5),3,IF(L3<=$S$4,4,""))))

 

[DarrenK]

I am ok with 1 IF statement, any more than that and I get really confused. I'm going to keep this one though in case I have to do more. Thank you Fluff, that took care of it and I don't have to save it as a macro file anymore.

Thanks again!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Woodpusher147]

Hello
I hope you can help with a similar but (in my not super excel mind) more complicated issue.

I have a the above spreadsheet to track performance which has 11 columns and however many staff there are as rows.
The sheet has conditional formating to shade each cell based on score, that's all done ok.
I also have data validation as Bedroom does not score in columns 4,5,6,7,8 - Upstairs & Downstairs do not score in 9 or 10

However, I now need to have an overall score by using the following Key
RED =0
AMBER=1
GREEN=3
YELLOW=5
The other complication is that columns 1,2,3 and 4 will score 4 times more than all other columns but I guess I can do this by having the formula/code populate hidden cell which I can then simply refer to this x4 for the cells in rows 1,2,3,4

Ive looked at an old thread which is titled "Changing cell value based on the color of another cell" and has an interesting
User Defined Function in VBA by Joe4. This could work as I dont want the code to run on all the sheet but I really need help

Rick Rothstein and sheetspread also commented and helped


I hope you can help as my head is now hurting


Thank you for any replies

Simon

 

[Fluff]

As this is totally different from the original question, please start a new thread. Thanks

 

[Woodpusher147]

As this is totally different from the original question, please start a new thread. Thanks

OK done, I just didnt want to repeat a similar question but all good.
ILl be very happy if I can get it sorted