Сохранить условное форматирование при вырезании-вставке

У меня есть данные из разных источников, которые я пытаюсь сопоставить, чтобы лучше понять, какие поля у них общие, а какие нет. Чтобы было удобнее видеть строки, я настроил правило условного форматирования следующим образом:

Правило:

=MOD(ROW(),2)=0

Применяется к:

=$1:$1048576

Действие:

Покрасить в зелёный

Это отлично работает… до тех пор, пока я не вырезаю и вставляю блок ячеек в одном столбце или другом. “Умное вырезание и вставка” Excel всё ломает, либо дублируя правила, либо удаляя секции из области, и мне снова приходится исправлять условное форматирование. Как я могу перемещать данные, не меняя правила условного форматирования?

Мне удалось найти одно решение, я записал этот макрос:

Sub FomattingRules()
'
' Макрос FomattingRules
'
' Горячая клавиша: Ctrl+e
'
    Cells.Select
    Cells.FormatConditions.Delete

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Технически это работает, но я бы предпочёл найти решение, которое не требует от меня периодически нажимать ctrl+e. Но это лучше, чем сбрасывать правила вручную, я думаю.

Нет хорошего способа обойти это. Я рекомендую использовать Копировать+Вставить значения, затем удалить содержимое оригинальных ячеек. Это сохранит все форматы без изменений, но успешно переместит содержимое ячеек. Это немного громоздко, но ваш другой вариант – заново настраивать условное форматирование каждый раз, когда вы вырезаете и вставляете. Вы решаете, какой вариант менее трудоемкий.

Не обладая таким талантом программирования, как некоторые из вас, я решил проблему с помощью короткого двухэтапного процесса. Сначала я создал одну дополнительную строку данных, отделённую от поля данных, с которым я работаю над своим проектом. Я удостоверился, что эта строка данных имеет правильное условное форматирование, которое я хочу использовать по всему полю данных. Для удобства я назвал эту строку “Стандарт”.

Затем я просто выделяю строку “Стандарт”, выделяя все ячейки с той формулой, которую я хочу скопировать. В-третьих, я использую функцию “Кисть формата”. Я нажимаю “Кисть формата” (верхний левый угол экрана под Вырезать и Копировать), а затем выделяю ячейки, формула которых должна совпадать с формулой, сохранённой в строке “Стандарт”.

Это не автоматизировано, но как только вы создаёте строку “Стандарт”, сохраняя желаемое условное форматирование, применять это к даже очень большому полю данных очень легко всего за два шага.

В Excel 365 есть новый режим вставки: “Специальная вставка – Объединить условное форматирование (G)”.

редактирование 1: Я не уверен, какие требования к этой новой функции. Я использую установку Office по умолчанию. На самом деле, она также присутствует в Excel 2016. Номера сборок:

Excel 2016 (16.0.5071.1000)
Excel 365 (16.0.13127.20266)

Я думаю, что если вы выполняете условное форматирование по столбцам (т.е. $G:$G), а не упоминаете строки, когда вы вырезаете и вставляете строки, это должно сохранять условное форматирование. Это при условии, что вы не вырезаете и вставляете столбцы (т.е. если вы используете $A:$D).

Если я копирую выделенные ячейки с условным форматированием и вставляю их в ‘Google Sheets’, условное форматирование остаётся с ячейками. Затем я могу скопировать и вставить их обратно в таблицу Excel, где условное форматирование также сохраняется.

Это наименее громоздкий и самый быстрый способ, который я нашёл.

Самый простой способ – вставить непосредственно в строку формул, если вы вводите данные ячейка за ячейкой.

Скопируйте выделенные ячейки, выберите специальную вставку, затем выберите “всё с использованием исходной схемы”. Это работает идеально.

Исходную проблему, т.е. маркировку нечётных и чётных строк разными цветами, можно решить без условного форматирования и, следовательно, без сопутствующих проблем.

Преобразуйте соответствующую область в “таблицу”:
Выберите желаемую область, затем нажмите ctrl + t (или вы можете нажать кнопку формат как таблицу на вкладке главная или нажать кнопку таблица на вкладке вставка). Затем выберите стиль таблицы с чередующимися цветами для строк.

Я нашёл новый способ сделать это!

Вы блокируете ячейки, которые условно отформатированы. Когда вы вырезаете и вставляете, условное форматирование остаётся прежним!

У меня была такая же проблема, и я нашёл небольшое решение – возможно, оно будет полезным для вас:

Вместо копирования и вставки: выделите последнюю строку/столбец (или диапазон) и используйте действие Заполнить вниз/Заполнить вправо (Сочетание клавиш: strg+< / strg+>). Тогда форматирование также будет перенесено.

Это также можно сделать с помощью макроса, т.е. для диапазона строк:

Range("D2:D15").Select
Selection.FillDown

Вместо вырезания и вставки: выберите строку или столбец и ПЕРЕМЕСТИТЕ его, наведя курсор на край выделения (появляется курсор с крестиком и стрелками) и перетащите строку/столбец, включая нажатие SHIFT. Таким образом, это не классическое вырезание и вставка, а перемещение. Условное форматирование должно сохраниться.

Блокировка ячеек не является решением, протестировано и не работает!

Если вы вставите, используя “Специальная вставка – без форматирования”, это не повредит уже отформатированные ячейки, и вам не нужно беспокоиться о том, чтобы пользователю пришлось включать макросы, чтобы поправить форматирование. Легкий вариант решения проблемы для уже развернутых решений!

Это очень неэлегантно, но также очень быстро и, насколько я могу судить, работает идеально…

Скопируйте свои ячейки с условным форматированием, вставьте их в документ Word, скопируйте ячейки из документа Word, вставьте их в целевую таблицу Excel.

Я думаю, что это вопрос того, как вы ищете ответ. Это идеально сработало для меня: https://www.extendoffice.com/documents/excel/3686-excel-remove-conditional-formatting-but-keep-format.html#a1