excelvbaexcel-formulaconditional-formatting

How to auto assign a number to a row based on conditional formatting

I have a contact form where I want to assign a priority contact based on how many days it has been since the last contact.

enter image description here

In the example image I have the the conditional formatting in Column E which is Excel's standard colour scale system. What I would like is for the cells in Column A to be auto filled with numbers based on the colours of the scale, so anything formatted in red would be a higher priority than anything in Green.

I am not versed in VBA but am willing to use it if someone can provide guidance. Is this even possible?

Solution

  • In Excel, you cannot base a number on a colour, but you can base a colour on a value. So, using the colour as an input is not a good idea.

    However, the following formula might be interesting for you:

    =MATCH(E2;SORT(E$2:E$5);0)

    As you can see, you take the days since last contact, order that list, and take the index of every value out of this sorted series, resulting in a kind of priority. Is this what you want?

    Hereby a screenshot of the results:

    Screenshot