Make cell color the same as another cell color and keep formula

0
votes

In Excel 2007, I've conditional formatting for column Q:

  • values > -5 = green
  • values between -5 and -25 are yellow and
  • values less than -25 are red.

I want cell P5 to have the same color as Q5, Cell P6 to have the same color as Q6 and so forth.

The value in cell P5 is dependent on a formula (K5-C5),
the value in cell P6 is dependent on a formula (K6-C6) and so forth.

How can I change the color of the cells in column P so they are equal to their "matching" cells in column Q, without ruining the formulas created in column P?

2
excelvbaexcel-formulacolorsconditional-formatting
What has VBA got to do with this? – Siddharth Rout
If it can be solved without VBA, great. It's just that the closest I've come to a solution has been via VBA. – Lotus
If I have understood you correctly then you do not need vba for this. See the answer that I posted. You may have to refresh the page... – Siddharth Rout

2 Answers

0
votes

If I have understood you correctly, then you don't need VBA for this. Simply highlight the relevant range and not just column Q while conditional formatting. See this example

enter image description here

0
votes

I have not bothered with cutoffs since for example you have not specified the formatting you required for a cell in ColumnQ with value -5.

For consistency with the details in your link, I have assumed that the likes of values >-5 refer to -5% rather than -5.

To change the color of the cells in column P so they are equal to their "matching" cells in column Q, without ruining the formulas created in column P apply Conditional Formatting (which does not alter the actual cell content, only its appearance).

To format ColumnP cells according to rules determined by ColumnQ cells adjust the range to which the formatting is applied. Roughly, determine the format for Q and apply that to P:Q.

Since three colours are involved CF will require at least two rules where determined by formulae (a third colour could be applied by 'standard' fill, which CF rules override).

Formula rules as below appear to meet my limited understanding of your requirements:

Green: =$Q4>-0.05
Yellow: =AND($Q4>-0.25,$Q4<0.05)
Red: =$Q4<0.25

For the example I chose the applicable range for each as: 

=$P$4:$Q$12

SO26714147 example

Note:

  1. These do not directly address transition values such as -5%
  2. Empty cells will also be formatted
  3. The order of the rules makes a difference