Ask Your Question

Copyable conditional color scale [closed]

asked 2014-05-21 21:43:21 +0200

foben gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 04:12:03.781960

1 Answer

Sort by » oldest newest most voted

answered 2014-05-21 23:26:20 +0200

CEAuke gravatar image

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.

edit flag offensive delete link more


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!

foben gravatar imagefoben ( 2014-05-22 13:00:06 +0200 )edit

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.

CEAuke gravatar imageCEAuke ( 2014-05-22 13:09:17 +0200 )edit

Question Tools



Asked: 2014-05-21 21:43:21 +0200

Seen: 6,538 times

Last updated: May 21 '14