Fill Down without changing background color

[RussG2]

I have a spreadsheet with columns in which cells alternate colors from one row to the next: blue, lavender, blue, lavender, etc. I would like to be able to Fill Down just the data value in the cell but not the background color or other formatting. Is there a way to do that? It seems to me I used to be able to do that but now when I fill down the cell background cor is also filled down. Do I mis-remember? Thanks.

[LeroyG]

@RussG2

Fill Down just the data value

• If there is no increment in the data value: copy, and paste only values.
• If there is increment in the data value (or the cell content is formula): enter the data value in the two first cells (one blue and one lavender), select both of them, and fill down.

[Lupp]

Use ConditionalFormatting with a formula like ISEVEN(ROW()).
If you want to cycle through more than 2 colors, use the MOD() function for a CF with additional conditions
See attached example:
cfERxampleColorChangeByRow.ods (9.3 KB)
.

[RussG2]

Thanks for the suggestion. I took a look at that and it hurt my brain. It’s too hard for me, too complicated. I was hoping there was a simple way just to lock a cell’s background color.

[Lupp]

@RussG2

Thanks for the suggestion. I took a look at that and it hurt my brain. It’s too hard for me, too complicated. I was hoping there was a simple way just to lock a cell’s background color.

Well, I do such things the way I described. And the CF Is copied/filled to whatever ranges then without additional precautions. The attributes are not hard-set but overlaid.

Of course, you may prefer the way @LeroyG described.
However, there is no simple way, imo, to “lock” (preserve) predefined attributes for a range you want to FillDown (dragging the handle with the mouse e.g.).

You can do it with the help of UserCode. Not simple enough. Not recommendable if not the task is reoccurring many times a day
.
Or You can do it in a three-step process.

1. Select the TARGET range already attributed the way you like. Copy it. (Ctrl+CF by default)
2. Select the source cell from where you want to fill down into exectly the target range previously selected. Fill down from there as intended from the beginning. (Use NO Copy/Paste here!!)
3. Select the first cell of the original TARGET range. Order Paste Special... (Ctrl+Shift+V by default). Disable all the offered Options. (This is not about the radio buttons under Shift Cells or Operations!) Enable only the option Formats. OK.

Simple enough?

(A: In fact I spent more time describing this than you may save within a year applying the result.
B: Yo can also prepare your values or formulas in a different column and then Copy/Paste them to the target range not touching the formats now.)