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:
Formula: = B2 < D2
=$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:
Formula: = B3 < D3
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):
Formula: = B4 < D4
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?