Forum Discussion
Conditional Formatting 3 Colour options
Has anyone any guidance on how I can use Conditional formatting to do the following:
Compare a value in a cell to another cell and provide a red amber green fill based on being greater than, Equal to or lower than the cell it is compared to. I want to do this to a range of cells where the value may be different in each cell and is being compared to a different cell in eache instance (example below:
Reference cell - 3 4 5 3 5 3 4
Value - 3 5 4 2 5 5 7
Fill Colour - A R G G A R R
The only was I have foud to do this so far is 3 conditional formatting formulas per cell (using formula for < = >) and I then cant copy all 3 rules to the other cells as the cell references are fixed ($B$3)
Thanks
Having created 3 new rules for each scenario is good.
To copy the rules to the other cells:
-Change absolute references to non-absolute.
- Define the range of application of these rules.
As you can see, I removed the "$" sign in the original formula and I then selected the range of application in the "Applies to" option.
Unfortunately that didnt work as you can see from the screenshot below. There does not seem to be any logic to how the rules apply. Row 11 is correct when compared to Row 8, i.e B11>B8 so is green. However, the rules are not working for Row 12, for example Cell E12 needs to show Green as is is > Cell E8
Interestingly I cant delete the $ from the applies to range B11:S22, not sure if this makes any difference.
Hi,
Try to fix the row refence of that cell you are comparing the results with. I assume its B8. Write it as B$8.
You should leave the "Applies to" range as absolute as well, so don't worry about that.
