Excel - Условное форматирование - вставка строки

Ответ 1

Я знаю, что это старый поток, но здесь другое решение, которое супер просто и отлично работает.

Просто вставьте новую строку или столбец по желанию. Затем выберите и скопируйте строку/столбец с правильным условным форматированием. "Прошлое" в новую строку/столбец, которую вы только что создали, и выберите опцию "Все слияния условных форматов". Ваши условные правила форматирования теперь должны автоматически обновляться.

Счастливый Excel-ing =)

Ответ 2

Это общая проблема с условными форматами. Если вы вставляете строки или перемещаете вещи, Excel предполагает, что вы хотите, чтобы условный формат перемещался с ячейками, и что вы хотите вставить ячейки с их исходным форматированием (или вообще ничего).

Таким образом, он пытается изо всех сил разбить форматированный диапазон в соответствии с изменениями. К сожалению, "его лучшее" не очень хорошо. В счастливых случаях ваши правила форматирования дублируются, если вы не заметите; в неудачных случаях они ломаются для некоторого или всего применяемого диапазона.

Это особенно проблема, если вы работаете с ListObjects (a.k.a. "Таблицы Excel" ). Вставьте несколько строк, немного измените порядок, перетащите некоторые значения, и в следующий раз, когда вы заглянете в свой условный список форматирования, у вас есть десятки и сотни повторяющихся правил. (пример: http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/)

По моему опыту, самый быстрый способ исправить беспорядок - удалить все правила и воссоздать их (или нет).

Некоторые побочные эффекты:

• Диапазон применений всегда абсолютен. Это не так.
• Чтобы ухудшить ситуацию, условные форматы обрабатываются как изменчивые формулы, что означает, что они пересчитываются во многих случаях (открытие другого файла, прокрутка и т.д.). Если вы не заметите разделение, они могут значительно замедлить все приложение через некоторое время.
• Если вы идете на VBA, вы, вероятно, захотите использовать событие Worksheet_Calculate, по крайней мере, если ваши формулы относятся к другим листам (знайте имена!)

Ответ 3

У меня возникала эта проблема при попытке создания отчетов - как только они закончены, их не нужно менять, но пока я их создаю, я продолжаю добавлять новые строки, и каждая новая строка объединяет условное форматирование.

Это отнюдь не хорошее решение, но это было лучшее, что я мог найти, не прибегая к VBA - это было:

a) Сделать правила условного форматирования применимыми ко всему столбцу или более за раз

например, вместо установки условного форматирования на C2 и C17, поместите дополнительный столбец и напишите "этот" в строках 2 и 17, а затем установите форматирование для всего столбца C как "если другой столбец говорит "этот", тогда примените этот формат "

b) Измените значение "Применяется к" как "$ C $1: $C $2".

c) Внесите изменения и вставьте строки и прочее

d) Затем вернитесь назад и измените значение "Применяется к" как "$ C: $C

Таким образом, в то время как вы меняете вещи и добавляете вещи, условного форматирования там нет, но потом вы возвращаете все это позже.

Если на более поздний срок вам нужно добавить еще несколько строк, сначала измените их с $C: $C на $C $1: $C $2, затем внесите изменения, а затем верните их в $C: $C. Таким образом, вам не нужно полностью переписывать все правила форматирования с нуля, как если бы вы делали то, что я делал ранее, что просто удаляло их все, проклинало и начиналось снова;)

Очевидно, что если вы планируете вставлять строки вверху в строке 1 или 2, это не сработает, но вы всегда можете установить его в некоторые другие строки, которые, как вы знаете, вы не измените.

Ответ 4

Хотя это довольно старая тема, мои листы Excel также страдали от дублирования условного форматирования при вставке новой строки.

Мне удалось обойти это. Позвольте мне поделиться им с другими, это тоже может помочь.

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

В моем случае я хотел отобразить горизонтальную границу, когда значения двух соседних строк различаются, например:

=$A2 <> $A1

Если я использую OFFSET для ссылки на предыдущую строку, все правильно, никаких дублированных условных правил форматирования.

=$A2<>OFFSET($A2; -1; 0)

Я фактически помещаю эти условные формулы форматирования в скрытый столбец, но результат должен быть тем же.

Ответ 5

Я понял, что строка вставки не вызывает разделение условных правил форматирования. Я также копирую строку и вставляю в вставленную строку, которая это делает. Если я выберу специальную пасту и выберу только формулы, ее работа прекрасна.

Тем не менее, я задаюсь вопросом, будет ли когда-либо понадобиться использовать "INDIRECT" или "OFFSET" в поле "Применяется к" условного форматирования. Если это так, это будет проблемой.

Ответ 6

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

В моей электронной таблице Excel 2010 я вводил даты в столбец B. Иногда я толкнул запись даты, поэтому я хотел условно отформатировать их. Первоначально я выбирал диапазон (B2: B1960), поэтому моя формула в правиле условного форматирования была бы "= B2: B1960 > СЕГОДНЯ()".

Хорошо, это сработало, пока я не поместил новые строки между существующими строками. Правила будут разделяться так же, как описано ОП. Мне довелось посмотреть на несколько разных веб-сайтов и найти сайт Microsoft Office, который указал мне на ответ. Он упомянул выделить диапазон, который вы хотите отформатировать, но измените формулу на "= B2 > СЕГОДНЯ()".

Сменяя формулу, я могу теперь вставлять новые строки между существующими строками и не получать разделенные правила, как и раньше. Вот ссылка на эту веб-страницу. http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx

Ответ 7

Что работает для меня, когда вы вставляете строку, не копируйте форматирование из другой строки. Только copy-special вставляет формулы. Условное форматирование затем не распадается.

Ответ 8

Мое решение этой точной проблемы заключалось в очистке форматирования от исходной копии. Шаги:

• Скопировать источник в буфер обмена
• Откройте новый документ excel
• Специальная вставка, выберите формулу (копирует значения и формулы и опускает форматирование)
• Скопируйте это в буфер обмена
• Теперь вы можете либо вставить обратно в исходный код перед его использованием, либо вставить пустые строки в лист с условным форматированием (обратите внимание, что только вставка строк не изменит диапазон в условных правилах форматирования) и вставьте содержимое буфера обмена в новые строки.

Это работало для меня с использованием MS Excel 2016

Ответ 9

Здесь похожая тема, которая может помочь вам на правильном пути:

Как использовать событие Worksheet_Change для условного форматирования?

В нем описывается способ форматирования стиля R1C1, на который не могут влиять вставки (непроверенные), а также подход VBA, упомянутый в комментариях.

Ответ 10

Я нашел простой процесс, который, похоже, работает последовательно для вставки новых строк или столбцов И сохраняет непрерывность условных правил форматирования (по крайней мере, в Office 2010):

• Сделайте простую "Вставку" вашего нужного количества новых строк или столбцов выше, ниже или слева или справа от строки или столбца, содержащего условное форматирование, которое необходимо сохранить.

ПРИМЕЧАНИЕ а) Ваше условное форматирование автоматически применяется к вставленным строкам или столбцам       без необходимости делать что-либо еще. Форматирование должно быть унаследовано       из соседнего ряда или столбца.    б) Форматирование любых границ также должно быть скопировано во вновь вставленные ячейки.

2. Выберите строку, столбец или диапазон (щелкнув по нему), который находится рядом с недавно вставленными, и который содержит скопированное условное форматирование (и формулы и данные, если применимо).

3. Наведите указатель мыши на нижний левый или нижний правый угол выбранного диапазона, пока не увидите знак "+" плюс (не путайте его с гаджетом размера строки с похожим видом).

4. Щелкните левой кнопкой мыши и удерживайте "+" и перетащите нужные строки, столбцы или диапазон для форматирования, затем отпустите.

ПРИМЕЧАНИЕ. Я создаю правила условного форматирования, ссылающиеся только на одну ячейку: Пример) в поле "Формат значений, где эта формула истинна:", создайте правило, такое как... = AND ($ B8 = ", $C8 =" ", $D8 =" ", $K8 < > " "), где это правило Действует, чтобы сказать диапазон... = $B $8: $D $121, $J $8: $M $121.

Ответ 11

Что вам нужно сделать: 1) вставить новую строку 2) скопировать строку, которую вы хотите клонировать 3) вставить специальное "Условное форматирование слияния"

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

Ответ 12

В 2013 году, когда вы найдете, что ваши правила форматирования были разделены/дублированы, определите новый namedrange для каждого формата. Затем установите для параметра value = [Именованный диапазон]. Excel заменит именованный диапазон фактическим диапазоном. Затем удалите повторяющиеся форматы.

Ответ 13

Я создаю решение, в котором будут задействованы другие со смешанными уровнями умения Excel, поэтому мне нужно было что-то более легкое и последовательное, чем их помнить о том, чтобы скопировать и вставить определенный способ.

В Excel 2016 вы можете вставить таблицу из выбранного диапазона, что дает вам преимущество использования структурированных ссылок (например: tblTOP [Тип]), чтобы ссылаться на данные в столбце "Тип" таблицы с именем tblTOP).

Затем я нашел этот ответ на сайте Microsoft, который показывает эффективный способ ссылки на таблицу в части формулы CF: условное форматирование структурированных ссылок

Итак, с установленным...

Это то, с чем я работаю:

Столбцы tblTop

Я установил свое условное форматирование, так что, когда я изменяю значение типа где-нибудь между A и E, он изменит эту строку на соответствующий цвет. (Пример показан: B превращает строку в зеленый)

Это было выполнено с использованием формулы = INDIRECT ("tblTOP [Тип]") = "B"

Когда я пошел добавить строку, хотя, я получил то же форматирование применяется к этой второй строке :(.

Сломал CF между двумя рядами

CF, которая сработала

Короче говоря, следующая формула - это то, что я придумал, чтобы применить правило CF к этой конкретной строке и не влиять на добавление или удаление строк:

= ДВССЫЛ ( "tblTOP [@type]") = "В"

Добавление "@" перед структурированной ссылкой ведет к тому, что происходит только для данной строки. Ницца.

Поэтому теперь я могу выполнить вкладку или использовать контекстное меню, чтобы добавить новую строку, и она ожидает выбора типа, чтобы определить цвет только для этой строки.

Добавлена новая строка

Новая строка работает как ожидалось

Я не тестировал его при вставке ячеек, поскольку цель этой таблицы заключается в том, что конечный пользователь должен вводить данные и добавлять/удалять строки по мере необходимости, поэтому я не могу сказать, будет ли это работать с вставкой строки.

Надеюсь, это поможет кому-то с условным форматированием в таблице.

Ответ 14

Я согласен с тем, что было опубликовано ранее; скопировать и вставить значения (или вставить формулы) будет полностью работать, чтобы не разделить условное форматирование.

Я немного ленив. И я не хочу, чтобы те, кто использует мои таблицы, должны были это сделать. Я также не уверен, что они будут помнить, что нужно копировать и вставлять значения. :(

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

Поскольку этот макрос запускается каждый раз при открытии рабочей книги, пользователю не нужно изменять способ копирования и вставки. Им не нужно знать, что макрос даже там. Им не нужно вручную запускать макрос; это автоматически. Я чувствую, что это создает лучший пользовательский интерфейс.

Имейте в виду, что этот код необходимо скопировать и вставить в модуль "Эта рабочая тетрадь"; а не регулярный модуль.

Private Sub Workbook_Open()
'This will delete all conditional formatting and reapply the conditional formatting properly.
'After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
'weeks there are so many conditional formattings that Excel crashes and has to recover.

Dim ws As Worksheet, starting_ws As Worksheet


Set starting_ws = ActiveSheet   'remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "InvErr" Then
        ws.Activate
        Cells.FormatConditions.Delete
        ''Every Other Row Tan
        Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
        Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
        Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
        Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
        Range("A4:M203").FormatConditions(1).StopIfTrue = False

        ''Highlight Duplicates Red
        Columns("B").FormatConditions.AddUniqueValues
        Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
        Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
        Columns("B").FormatConditions(1).Font.Color = -16383844
        Columns("B").FormatConditions(1).Font.TintAndShade = 0
        Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Columns("B").FormatConditions(1).Interior.Color = 13551615
        Columns("B").FormatConditions(1).Interior.TintAndShade = 0
        Columns("B").FormatConditions(1).StopIfTrue = False
    End If
Next

starting_ws.Activate   'activate the worksheet that was originally active
Application.ScreenUpdating = True

End Sub

Ответ 15

Это работало достаточно хорошо для меня...

Sub ConditionalFormattingRefresh()
'
' ConditionalFormattingRefresh Macro
'

'Generales
Dim sh As Worksheet
Dim tbl As ListObject
Dim selectedCell As Range
Set sh = ActiveSheet
Set tbl = Range("Plan").ListObject
Set selectedCell = ActiveCell

'Rango a copiar
Dim copyRow As Range
Set copyRow = tbl.ListRows(1).Range

'Rango a restaurar
Dim startCell As Range
Dim finalCell As Range
Dim refreshRange As Range
Set startCell = tbl.DataBodyRange.Cells(2, 1)
Set finalCell = tbl.DataBodyRange.Cells(tbl.ListRows.Count, tbl.ListColumns.Count)
Set refreshRange = Range(startCell.Address, finalCell)

'Ocultar procesamiento
Application.ScreenUpdating = False
Application.EnableEvents = False

'Borrar formato corrupto
refreshRange.FormatConditions.Delete

'Copiar
copyRow.Copy
'Pegar formato
tbl.DataBodyRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Retornar a la normalidad
selectedCell.Select
    Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Ответ 16

Я получил его для работы в Excel Mac 2011 следующими шагами.

• вставка новой строки
• копирование одного над ним (с уже примененным условным форматированием)
• выделение новой строки и ПАСПОРТ СПЕЦИАЛЬНОГО -> СОХРАНЕНИЕ СОСТОЯНИЯ.

Правила CF остались нераскрытыми и обновленными, чтобы включить дополнительную строку.