Apply the color rule to all cells at once

[tyah]

hi
I work on spreadsheet and i want to use the conditional color scale to color code rows of cells based on their value. If cell value is low green and if high red.

I used the conditional color scale but i have to repeat it for each row. How apply the color coding to all rows at once.

I need the result to be something like this b2 … j2, b3 … j3 and not b2 … j20
thanks!

[Hrbrgr]

Conditional Formatting Guide

How to apply a Color Scale Conditional Formatting

[karolus]

@Hrbrgr : OP asks for apply colorscale for each row seperatly…

[joshua4]

It’s annoying…almost as if LO is fighting against you to do this. Perhaps someone will have a full solution, but here is a workaround short of hand entering every single conditional range:

1. Set up 1 row that is the way you want it with the conditional formatting.
2. Select the used cells (your formatted part) of that row and the “same” cells of the blank row below it.
3. Copy that to the clipboard.
4. Paste just below the blank row you included in your copy.
5. Once done with enough rows, ctrl+click the left row indicator for each of the unwanted blank rows, right+click on one of the row indicators and select Delete Rows.

So, the idea is to include a non-formatted row in your copy so that LO doesn’t merge the conditional formatting region, then delete the “barrier” rows to end up with what you want.

Now you can have, say, numbers in the 10’s in one row and in the 1000’s in another, but the rows will flag colors independently.

Oh, if you already have values entered, just do the above procedure in a new area/sheet and then copy and paste values back over it using Paste Special>Values Only…adjust for formulas, etc. You get the idea.

[tyah]

ill try this tonight and see how it goes and update you. thanks a lot!

[mikekaganski]

@joshua4

almost as if LO is fighting against you to do this.

yes it does; and that is a side effect of (incomplete/wrong) deduplication of conditional formatting rules. I know that because I implemented that deduplication myself in tdf#95295.

[LeroyG]

• Set the condition in B2
• Copy and paste (don’t drag) B2 in B2:B20
• Drag B2:B20 until J20
• Enter the values

@joshua4

if you already have values entered

You will need two auxiliary rows.

• Set the conditional format in B21
• Copy and paste (don’t drag) B21 in B21:B22
• Drag B21:B22 until J22
• Copy B21:J22
• Paste on B2:J20 as unformated text selecting only Formats

Tested on version 6.4.5.2 on Linux 4.12. Yes, a bit old.
Re-tested on version 7.1.8.1 on Windows 7.

---

EDIT:
Formula is
MAX($B2:$J2)=CELL("contents";B2)
MIN($B2:$J2)=CELL("contents";B2) with style “Good”.

color rule CF.ods (10.5 KB)

[joshua4]

@LeroyG certainly has a solid interpretation of the OQ (original question). It’s different from mine.

joshua4: If OQ is color comparison within each row with each row independent from each other row.
LeroyG: If OQ is color comparison within each column, with each column independent from each other column, such as

However, @LeroyG’s method of starting by formatting only an individual cell will work either way.

1. Compare within columns by copying (not dragging) a single formatted cell across (one at a time or by highlight/paste) then dragging the (used part of the) row down
2. Compare within rows by copying (not dragging) a single formatted cell down then dragging the (used part of the) column across.

So LeroyG’s method is far easier. I think circumstances would dictate whether it is easier to copy values/formulas onto formatted cells, or copy formatting onto value/formula cells.

[LeroyG]

@joshua4

within each column

No.

color rule CF.ods (13.0 KB)

[tyah]

hi i have tried it and it works. however, i get a gradient colors. Id like to have only 2 colors even for same values

for example if 1 row had 2 5 20 55 55 then cell inclding 2 will be green and cells including 55 red. the rest is unchanged.

[tyah]

worked like a charm. thank you all for your valuable input.