Jul 25 2019 05:20 AM
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
Jul 25 2019 05:35 AM
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
Jul 25 2019 06:54 AM
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
Jul 25 2019 07:17 AM
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:
Hope that Helps
Nabil Mourad
Jul 25 2019 07:23 AM
Jul 25 2019 07:27 AM
SolutionI am happy I could help you Chris
Please mark the answer as "Accepted " and Like .
Thank you :)
Good Luck
Nabil Mourad
Sep 14 2022 11:51 AM
I'm on a MacBook using MacOS Monterey 12.5.1. Excel for Mac 16.65
I'm trying to count the number of cells in a row that I have assigned a color. This tip doesn't work as the Find function (to the right of home - "find & select") doesn't provide any options when Options on the bottom right is selected. I do get the "within", "search" and "look in" drop down boxes but selecting Options results in a "Find What" box. The drop down from that is a blank gray selection box.
Please help!
Sep 14 2022 02:21 PM
For what it's worth, @nabilmourad has not had a post or response on these boards for over two years.
Rather than picking up on a thread that itself is now ancient history, you would be better off starting an altogether new thread with your question, spelling it out a bit more fully in the process. For example, "this tip" that you refer to --- what is it?
Jul 25 2019 07:27 AM
SolutionI am happy I could help you Chris
Please mark the answer as "Accepted " and Like .
Thank you :)
Good Luck
Nabil Mourad