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

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
A UDF cannot "see" conditional formatting. Why not just use the CF rule to determine what value col M should be.
 

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
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,""))))
 
Solution

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Woodpusher147

New Member
Joined
Oct 6, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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


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
1633519851369.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
As this is totally different from the original question, please start a new thread. Thanks
 

Woodpusher147

New Member
Joined
Oct 6, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Forum statistics

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

Disable AdBlock

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
Top