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

#### DarrenK

##### Board Regular
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

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

#### DarrenK

##### Board Regular
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

##### MrExcel MVP, Moderator
Ok, how about in M3
Excel Formula:
``=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

##### Board Regular

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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### Woodpusher147

##### New Member

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

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

#### Woodpusher147

##### New Member
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

Replies
3
Views
247
Replies
31
Views
926
Replies
1
Views
583
Replies
15
Views
495
Replies
2
Views
522

1,148,257
Messages
5,745,707
Members
423,968
Latest member
CHHeights

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