Re: Cell (text contents) act to trigger multiple cells.

Problem Description:

Drop down list containing 5 stages of a typical sales cycle, i.e. client engaged, negotiation, prospect, Short-listed, Win/Close.
These are locate in column B and each cell in column B has a drop down list with these 5 options.

Now, based on which option is selected the corresponding cells in column C and D need to change. This change is defined as follows -

Cells in column C, need to change colour ( i.e. blue, orange or red)
Cells in column D, need to change to a percentage value (i.e. at intervals of 10%, 10%,20%, 30% ...etc to 100%)

Column C (corresponding cell i.e. if B3, implies change to C3 respectively). In this case cell colour needs to change to one of three colours listed above.
i.e. if cell in B2 has client engaged, C2 must show blue, Similarly if B2 has short-listed, C2 must show red.


Column D, Equally, depending on colour in Column C, the percentage must automatically populate.
Note, acceptable granularity is;

0-30% = Blue
31-50% = Orange
51-100% = Red


I have used conditional formatting options on a trial and error for testing the above, but not getting much progress. I suspect due to the multiple changes and different simultaneous changes i.e. colour and text, a Macro is most likely needed? (I have limited knowledge of VBA).

In closing there are about 200 rows that need to have this capability. I.e. Contents of column B trigger respective cells in Column C and D.

Hope this is clear and somebody here would be patient enough to walk me through the solution. Your effort in to arriving at a solution would be greatly appreciated.

thank you,

Hi,

Welcome to the Forum

It would be more clear to understand your problem if you can upload a workbook with sample (insensitive) data.

Hi Niall,

Refer attached workbook. Added some data validation and conditional formatting, which might help. Column C can be hidden if required.

In column D if you are inputting % values, then you need to modify the conditional formatting accordingly. Is that something you can work with ?

Hi Niall,

Refer attached workbook. I've made a slight modification to the probability rate to highlight the cells with 0% in Grey color. Is this something you can work with?

Hi Niall,

Welcome.. If you believe my assistance has helped, you can add to my reputation by clicking the * icon on the left hand corner below the post to show your appreciation.