Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Copyable conditional color scale

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?