Avatar of Yashy
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Help with creating a formula based on colour coding

Hi guys

I've got this big Excel sheet with loads of rules. In the spreadsheet attached, there is a Justification column that is colour coded. I have to create  another column called 'Grade'. Whatever the colour is in the Justification column, must also be added into the Grade column with the same colour. But along with that, I need the numbers put into the Grade column to. I've deliberately done an example of how it should look. So if Justification column is green coded, then the Grade column must also be filled green but with the right number, which in this case is 1. If justification column is red, then the grade column must also be red, with the associated number 4.

Can you help me with a formula that will help with filling in the correct content automatically into the grade column? I already have the justification columns completed if that makes sense.

Thanks for helping
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon
Ron Malmstead


Hi Ron,

Thanks for responding.

I don't believe this works by identifying the cell value with colour first and then filling in the adjacent cell with the same colour. I think there needs to be a value first, for the colours to be applied. I only have colours, and based on the colour, a value and colour will be applied to the column next to it.
Ejgil Hedegaard

You can use this formula in A2, copy down.
=CHOOSE({1,2,3,4},MATCH(B2,{"Keep rule","Investigate","Change rule","Remove rule"},0))

Open in new window

And then conditional format for the value to colour.
See sheet.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Ejgil, that's amazing! It works if I have the words in your quotes in the Justification column. Is there anyway it could work with anything written in there? As the one I uploaded was an example. The justification column does have a lot of content in there with explanations etc. If there is a way that would be amazing. If however you think the only way would be to do it this way, then I'll make changes to do accommodate it.

Thank you again.
Ejgil Hedegaard

The text in the quotes can be anything, but the formula requires exact match to the text in B2.

If there is not an exact match, the key words (keep, investigate, change, remove) could be searched in the text, and some if statements could define the grade number 1, 2, 3 or 4.
In this sentence "Investigate this rule before you keep, remove or change it", all 4 grade numbers could be the result, depending on the search order for the key words.
So a priority for the key words must be defined.

Another method could be to use the first key word found to define the grade number, but I think a formula for that will be very complicated.
Then I would go for a UDF function programmed in VBA (macro), which is much easier.

If you think either is the way to go, then upload a sample with some justification explanations.
And a description of key words, priority etc.

But I would suggest you separate justification and explanation, and use 3 columns, Grade, Justification and Explanation.
All columns could be coloured with conditional format using the number in the Grade column.

Ejgil, so I did try to add something like 'Keep Rule. asdfjk;asd jfk;a jkf' into the Justification column and it will give me an #N/A error. Don't worry if it's too difficult, but is there a way for it to search for a match within a set of phrases at all?

Apologies for the delay in responding.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ejgil Hedegaard

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Worked beautifully. Thank you so much Ejgil. I really appreciate it.