SOLVED
Home

assigning a numerical value to a colored cell

%3CLINGO-SUB%20id%3D%22lingo-sub-773357%22%20slang%3D%22en-US%22%3Eassigning%20a%20numerical%20value%20to%20a%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773357%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Day%20All%2C%3C%2FP%3E%3CP%3EI%20have%20a%20co-worker%20who%20has%20used%20'fill%20color'%20to%20assign%20a%20relative%20risk.%20Green%20%3D%20Low%2C%20Yellow%20%3D%20Medium%2C%20Orange%20%3D%20High%2C%20and%20Red%20%3D%20Significant%20based%20on%20the%20impact%20to%20the%20overall%20requirement.%20I%20am%20trying%20to%20find%20out%20if%20I%20am%20able%20to%20assign%20a%20numerical%20value%20to%20the%20colored%20cells%20without%20typing%20in%20the%20number%20into%20each%20cell%3F%20Green%20%3D%201%2C%20Yellow%20%3D%202%2C%20Orange%20%3D%203%2C%20and%20Red%20%3D%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-773357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773388%22%20slang%3D%22en-US%22%3ERe%3A%20assigning%20a%20numerical%20value%20to%20a%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382041%22%20target%3D%22_blank%22%3E%40ChrisBlair%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Chris%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20assign%20a%20number%20based%20on%20brackets%20of%20numbers%20in%20another%20cell%20or%20may%20be%20text%20options%20in%20another%20cell%20then%2C%20in%20an%20adjacent%20cell%2C%20you%20need%20to%20create%20a%20conditional%20If%20function%20(or%20could%20be%20a%20Vlookup%20with%20approximate%20match)%3CBR%20%2F%3Ethen%20you%20apply%20regular%20conditional%20formatting%20to%20the%20numbers%20column.%20Set%20the%20fill%20color%20to%20be%20the%20same%20as%20the%20Font%20color.%20Having%20both%20of%20the%20same%20color%20will%20make%20the%20numbers%20invisible.%3C%2FP%3E%3CP%3EThere%20are%202%20conditional%20formatting%20options%20that%20enable%20you%20to%20hide%20the%20numbers%20those%20are%20%22Data%20Bars%22%20and%20%22Icon%20Sets%22%20.%20If%20you%20click%20on%20Manage%20Rule%20%26gt%3B%26gt%3B%20and%20check%20the%20box%20for%20%22Show%20Bar%20Only%22%20or%20%22Show%20Icon%20Only%22%20but%20these%202%20types%20do%20not%20fill%20the%20cell%20with%20color.%3C%2FP%3E%3CP%3EIf%20you%20share%20a%20sample%20file%20I'll%20be%20able%20to%20give%20a%20more%20accurate%20answer%20based%20upon%20the%20work%20situation.%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773484%22%20slang%3D%22en-US%22%3ERe%3A%20assigning%20a%20numerical%20value%20to%20a%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20answer.%20I%20have%20attempted%20to%20attach%20a%20basic%20spreadsheet%20with%20cells%20filled%20with%20the%20colors%20I%20am%20using.%20I%20am%20not%20sure%20how%20to%20correctly%20write%20the%20'IF'%20statement%20for%20assigning%20a%20value%20based%20off%20the%20color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E
ChrisBlair
New Contributor

Good Day All,

I have a co-worker who has used 'fill color' to assign a relative risk. Green = Low, Yellow = Medium, Orange = High, and Red = Significant based on the impact to the overall requirement. I am trying to find out if I am able to assign a numerical value to the colored cells without typing in the number into each cell? Green = 1, Yellow = 2, Orange = 3, and Red = 4.

 

Thank you

 

Chris

5 Replies

@ChrisBlair 

Hi Chris

If you want to assign a number based on brackets of numbers in another cell or may be text options in another cell then, in an adjacent cell, you need to create a conditional If function (or could be a Vlookup with approximate match)
then you apply regular conditional formatting to the numbers column. Set the fill color to be the same as the Font color. Having both of the same color will make the numbers invisible.

There are 2 conditional formatting options that enable you to hide the numbers those are "Data Bars" and "Icon Sets" . If you click on Manage Rule >> and check the box for "Show Bar Only" or "Show Icon Only" but these 2 types do not fill the cell with color.

If you share a sample file I'll be able to give a more accurate answer based upon the work situation.

Hope that Helps

Nabil Mourad

 

@nabilmourad 

Thank you for your answer. I have attempted to attach a basic spreadsheet with cells filled with the colors I am using. I am not sure how to correctly write the 'IF' statement for assigning a value based off the color.

 

Any assistance is greatly appreciated.

 

Chris

@ChrisBlair 

Hi Chris,

When I recommended an If statement (before you attach a file), I assumed that you are coloring the cells based on a conditional formatting rule that reads a value. 
Now the situation when I look at your file is totally different because I cannot create a conditional rule that returns a number based on color (Except in VBA- I can send you a link for that but it's complicated).
So, alternatively, I am going to use a very simple technique to select All Cells with a specific color and type a number in All of them simultaneously:

  1. Click on "Find & Select" to the right side of the Home Tab
  2. Select Find...
  3. Click on Options >> The box expands
  4. Click on Format >> Select the Eye Dropper tool
  5. Click on one of the colors in the worksheet
  6. Click Find All >> The box Expands
  7. Hit CTRL +A >. you selected All cells of that color
  8. Close the Find box and Do Not Click anywhere
  9. Type the number (Say 1) Then Hit CTRL + ENTER (not enter)
  10. Repeat for each Color

 

Hope that Helps

Nabil Mourad

This is perfect!!! Thank you very much for the assistance.

Chris
Solution

@ChrisBlair 

I am happy I could help you Chris

Please mark the answer as "Accepted " and Like .

Thank you :) 
Good Luck

Nabil Mourad

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies