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,