Copyable conditional color scale

[foben]

I have a (very large) confusion matrix which I would like to colorize with the ‘Color Scale’ conditional formatting.
Each row has a different number of inputs (e.g. the sum of each row is different).
The row sum is stored in a cell to the right of the last row value.
I want to color the cells from white to black, complete black only being used if the cell value is equal to the row sum.

I achieved this for the first row by applying a color range formatting with 2 entries.
For the ‘left’ one I put Value of 0 and color white, and for the ‘right’ one Formula with ‘=BC3’ (which is the cell with the sum for the first row).
This works, but I have two problems:

1. The sum cell is colored in black, even though the range does not include it (it says C3:BA3, which is correct).
2. I can not copy this conditional formatting, because the formula value is not updated, but still points to BC3 for every other row (it should be BC4, BC5, etc.)

The first one is not that problematic, I could live with that.
But fixing the Formula value for each row is unfeasible, for I have more than 50 rows and many different matrices I would like to color.

Is there a way to enable the use of the Format Paintbrush in this case?

[CEAuke]

Hey man

I’ll try and break your problem into two pieces and give you the answer to the first.

1. You want a dynamic address in your formual (solved. I believe)
2. You want the formula to auto-recalc when you past (I can’t figure this out yet)

My proposal for the first one: =INDIRECT(“F”&ROW()) if you put something like that in, you dont specify a range that the system needs to update. Whenever it decides to reevaluate the formula, it will point to the relevant row.

The issue is point2. The only way I can get CALC to recalc that dynamic reference, is as follows:
Paste special formats over multiple rows
Then go format → conditional manage.
Then click on every row, edit and ok.
When you click the final ok to close the management of formulas’ all the rows that you edited (and OK’ed) will be reevaluated.

[foben]

Hi, thank you for your effort!! It works as you described - unfortunately, this does not reduce the effort enough
It’s a shame that there is no easier way to do this… I also can’t upvote your answer, I don’t have enaough rep. If there is actually no better way, I’ll accept yours.
Thanks again!

[CEAuke]

I was stuck on the low karma as well. Until I learned the the one thing I CAN do is to click the check circle under the up/down vote.