Link to home
Create AccountLog in
Avatar of Yashy
YashyFlag 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
Avatar of Ron Malmstead
Ron Malmstead
Flag of United States of America image

Avatar of Yashy


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.
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.
Avatar of Yashy


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.
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.
Avatar of Yashy


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.
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Yashy


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