Change Data in cell based on cell colour

[Mark F]

The sheet I have has different coloured cells depending on the value of the cell - ie conditional formatting.

I want to change the value in the cells based on the colour of the cell - ie if the sheet is 102%, the cell would be coloured green - I want to change the cell to have the value 10 but still stay green. Another example the cell value is 92% and red - I now need it to be -5 and red.

Any ideas.

Thanks

Mark

 

[parry]

Hi Mark, Im a bit confused by what your trying to achieve.

It seems you have multiple conditions using conditional format that sets various colours based on which condtion is met.

Why are you not changing the cell value based upon a formula, the same as your doing with a conditional format?

 

[Mark F]

I know it does sound odd and may well be impossible

I have to convert a sheet that has various percentage results which due to conditional formatting change the cell colour to green, yellow or red (traffic lights!?) depending on the % achieved - red being well below target, yellow being just outside target and green gbeing on target

I need to change the numbers in the green cells to 10, 3 for yellow and -5 for red in each cell, but would rather not have to type each one in.

I also need the cells to remain the same colour, ie green, yellow or red.

It might well be that I have to type the numbers in and manually change the cell colour fill.

Does that help?

Mark

 

[Yogi Anand]

Hi Mark:

There are several issues with you are trying to do -- as Parry has noted in his post also. One of the issues is the way you are trying to do involves circular refering.

So, how about if you consider first changing the cell entries (or preferably Custom Number Format) the entries based on % achieved, and then do Conditional Formating;

Or consider changing the entries based on % achieved using VBA, and also color code the cells via VBA as well.

I hope this helps.

 

[parry]

Yup Yogi's onto it. Circular reference is what I was thinking about

After your changing the values are you intending to remove the conditional formatting? Why I ask is that there may be a way in VBA to determine the cell colour (Im not sure with conditional formatting) for each cell, remove your conditional formatting then change the numbers and put back the colours although not necessarilly in that order.

That way you will end up with the colours and the values but they wont change according to conditional formatting. In other words, its as though you manually coloured the cells.

hth

 

[Mark F]

It isn't important for the conditional formatting to be present afterwards, as long as the cells themselves have the correct colour and number either, 10, 3 or -5.

I know it is a strange one - thanks for your help in thinking about it

Mark

 

[parry]

OK there are a few issues.

1. I dont think you can find the cell colour using code for formatting done via conditional formatting.

2. There are several shades of these colours (theres about 4 for green) so its difficult to know which ones you chose in conditional formatting.

However, I think this can be done by using the formula conditions you used in conditional formatting and the cells involved. For example it wouldnt be that difficult to look for cells that meet a particular condition and change the value to 10, 3 or -5 then colour them accordingly.

If you post back with the formulas you used in the conditional formatting and what cells the conditional formatting is over then i may be able to help.

 

[Phil]

Mark,

A quick way to achieve your goal could be to add a new column and use the following "if" formula to enter in the 10, 3 or -5 depending on your % targets (I have used >=100%=10, <100% and >=95%=3 & <95%=-5)

=IF(A4>=1,10,IF(AND(A4<1,A4>=0.95),3,IF(A4<0.95,-5,0)))

Once you have copied this formula down the rows you can assign a conditional format to the new column to assign the same colours for 10, 3 or -5 as the % column.

You can then hide the % column if you need.

Hope this is of helps.

Phil