Copy conditional formatting using a formula to the next column

Hi Team

example.png

Here is my data with red, yellow, green conditional formatting.

the formulas I am using are as follows (the table to the right of the photo is actually on another tab called 'Traffic light math')

RED - if less than =SUM(L4*'Traffic Light Math'!E$9)
YELLOW - if between =SUM(L4*'Traffic Light Math'!E$9) =SUM(L4*'Traffic Light Math'!E$8)
GREEN - if greater than =SUM(L4*'Traffic Light Math'!E$8)

Issue is when i use the format paint brush option to copy the conditional formatting, it doesnt adjust the cell references to the new cell. I have tried adjusting the absolute options with no luck.

Any help would be great

Matt

Welcome to the Forum mattdennis1402!

It is not possible to tell what cells in your screenshot correspond to the cells in the formula. Also, what cell are you trying to copy formatting from, and what cell are you trying to paint it to?

When you paint to another cell, the rule will still be the same. The formula will be same for all cells in the Applies To range, so depending on specifically what you are doing, the formula will not adjust the references to the new cell. Are you saying that you paint it, but it doesn't work as expected in the new cell?

I suggest you either attach an actual Excel file (see yellow banner at the top of the page), or show a screenshot with column and row headings.

All of the rules you have now are not what you describe in your post so I'm overhauling this. (For examples, rules applied to L10 reference $P4 instead of L4).

When you reference L4, you could use a relative reference, since you are only formatting row 10. However, for best practice, I am going to recommend you use an absolute reference for the row (L$4).

All references to value in Traffic light math should be absolute, as you show.

You only need one rule for each color. See attached file. Here is a quick view of the rules:

CF rules.jpg

You need to do some homework on how conditional formatting formulas work. When a condition applies to more than one cell, the formula is written as though it were only for the cell in the upper left corner of the range. You will note that after you paint to column M, the Applies To range is now $L$10:$M$10. Therefore the formula will reference L4, which is what is needed to format L10. If you experiment with the numbers you will find that the colors in M10 act exactly as you want.

This might be helpful.

Have we resolved everything? If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.