Conditional formatting: Applying rules to other cells (with changing references)

Question

I've googled this question and have come across various topics, but I haven't managed to apply it.

Here is my data:

I want to have the following rules in place (example for Company1):

• If B2 <= D2 (4.91%) -> red
• If B2 >= F2 (8.24%) -> green
• If D2 (4.91%) < B2 < F2(8.24%) -> orange

I want to apply the same rule to the other rows so cell references B2, D2, F2 change to B3, D3, F3 and then to B4, D4, F4 etc.

An example for how I've written the rule:

Rule

Formula: = B2 < D2

Format

Fill orange

Applies to

=$B$2 (= B2 doesn't change anything either)

Using the format painter doesn't really work. I click on cell B2 -> format painter -> select B3:B15. When I check the conditional formatting rule in any of the cells of B3:B15, it looks like this:

Rule

Formula: = B3 < D3

Format

Fill orange

Applies to

=$B$3:$B$15

That's fine for B3, but wrong for any of the other cells. So I can apply the conditional formatting rule via format painter to single cell, but not a range. This means clicking on B2, double clicking on format painter and then clicking on each individual cell in B3:B15 works and the so copied rule looks like this (for cell B4):

Rule

Formula: = B4 < D4

Format

Fill orange

Applies to

=$B$4

This is a an option for my example, but not if I have conditional formatting to apply to 200 rows.

Any idea how I can fix this?

Answer 1

You need 3 steps solution to apply Conditional Formatting.

How it works:

• Select Cell range O2:O11.
• From Home TAB reach to Conditional Formatting then New Rules.
• Step 1 Formula for Red color: =$O2<=$P2.
• Step 2 for Green color, apply this formula: =$O2>=$R2.
• Final step for Orange color, apply this formula: =AND($P2<$O2,$O2<$R2)

N.B.

You may adjust cell references in the formula as needed.