Копирование ячеек в excel по цвету

Нужно выделить ячейки одного цвета в столбце екселя, скопировать, и перенести в другой столбец. При этом, не нарушая порядок при копировании, например, если выделены ячейки a34, a35 и a37, то при копировании допустим в столбик b они вставлялись на то же место, но не трогая ячейку b36 (a36).
Ищу уже весь день, все не то. Нашел макрос который копирует несмежные ячейки, но проблема в том что вставляет их по порядку. Есть ли какие-то надстройки с уже готовыми решениями?

• 
  Вопрос задан

  более трёх лет назад
  

• 6390 просмотров

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

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

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

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

Теперь зададим формат ячейки пользуясь контекстным меню, для чего кликнем правой кнопкой мыши на ячейке и в открывшемся списке выберем «Формат Ячеек»:

На вкладке «Заливка» можно выбрать цвет фона и узор.

Рассмотрим несколько иную ситуацию. Допустим вы хотите скопировать цвет ячейки (и формат) с существующей и применить к своим ячейкам. Воспользуемся кнопкой на главной панели «Формат по образцу» («метелочка»):

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

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

Задать цвет ячейке (A1 окрашивается в Желтый):

Sub Макрос2()
Range("A1").Select
With Selection.Interior
.Color = 65535
End With
End Sub

Скопировать формат ячейки (формат A1 копируется на A3):

Sub Макрос1()
Range("A1").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial
Application.CutCopyMode = False
End Sub

Теперь комбинируя формат с операторами условия можно написать вычисления (например, суммирование) по условию цвета.

Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок ниже.

Спасибо.

Условное форматирование в Excel позволяет быстро форматировать ячейку (или диапазон ячеек) на основе значения или текста в ней.

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

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

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

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

Итак, приступим!

Копирование условного форматирования с помощью специальной вставки

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

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

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

Предположим, у вас есть набор данных, как показано ниже, где у меня есть условное форматирование, примененное к столбцу B (оценка по математике), чтобы все ячейки со значением более 80 были выделены.

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

Это легко сделать!

Ниже приведены шаги по копированию условного форматирования из одной ячейки в другую:

1. Выберите ячейку B2
2. Щелкните правой кнопкой мыши и скопируйте его (или используйте сочетание клавиш Control + C)
3. Выберите весь диапазон, в который вы хотите скопировать условное форматирование (C2: C11 в этом примере)
4. Щелкните правой кнопкой мыши в любом месте выделения
5. Нажмите на опцию Специальная вставка. Это откроет диалоговое окно Специальная вставка.
6. В диалоговом окне Специальная вставка выберите Форматы.
7. Нажмите ОК.

Вышеупомянутые шаги скопируют условное форматирование из столбца B и применит его к выбранным ячейкам в столбце C.

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

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

Копирование условного форматирования с помощью Format Painter

Format painter — это инструмент, который позволяет скопировать формат из ячейки (или диапазона ячеек) и вставить его.

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

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

Ниже приведены шаги по использованию средства рисования форматов для копирования условного форматирования из столбца оценки математики и применения его к столбцу оценки физики:

1. Выберите ячейку (или диапазон ячеек), из которой вы хотите скопировать условное форматирование
2. Перейдите на вкладку «Главная»
3. В группе «Буфер обмена» щелкните значок «Формат по образцу».
4. Выделите все ячейки, к которым вы хотите применить скопированное условное форматирование.

Совет от профессионалов: если вы хотите скопировать условное форматирование и вставить его в несколько ячеек или диапазонов (которые нельзя выбрать за один раз), дважды щелкните значок «Форматирование по образцу». Это сохранит активную программу рисования форматов, и вы сможете вставить форматирование несколько раз (если только вы не нажмете клавишу Escape).

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

Опять же, как и в случае со специальной вставкой, Format painter также копирует все форматирование (включая условное форматирование).

Проблема при копировании условного форматирования

В большинстве случаев у вас не возникнет проблем с копированием и вставкой условного форматирования из одной ячейки в другую.

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

Этот параметр позволяет создать собственную формулу, и форматирование применяется в формуле, возвращающей значение ИСТИНА для ячейки, и не применяется, когда формула возвращает ЛОЖЬ.

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

Например, в приведенном ниже примере я использовал формулу = $ B2> = 80, чтобы выделить все ячейки в столбце B, которые имеют значение выше 80.

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

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

Например, в этом случае я могу изменить формулу на = B2> = 80, и она должна работать нормально.

Если вам интересно, куда идет формула, перейдите на вкладку «Главная», а затем на «Условное форматирование».

В появившихся параметрах нажмите «Новое правило». В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу, чтобы определить, какие ячейки нужно форматировать.

Это покажет вам поле, в которое вы можете ввести формулу для выбранного диапазона. Если эта формула возвращает значение ИСТИНА для ячейки, она будет отформатирована, а если она вернет ЛОЖЬ, то нет.

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

И если вы видите проблемы с ним, проверьте используемую в нем формулу.

Надеюсь, вы нашли этот урок полезным!

Другие учебники по Excel могут оказаться полезными:

• Выделение строк на основе значения ячейки в Excel (условное форматирование)
• Выделите КАЖДУЮ СТРОКУ в Excel (используя условное форматирование)
• Как удалить форматирование таблицы в Excel
• Поиск и выделение данных с помощью условного форматирования
• Как применить условное форматирование в сводной таблице в Excel
• Выделите активную строку и столбец в диапазоне данных в Excel