Выделенный столбец Excel vba

Обновлено: 21.11.2024

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

Автоматическое выделение строки и столбца выбранной ячейки с помощью VBA

В этом примере показано, как можно программно выделить активный столбец и строку с помощью VBA. Для этого мы будем использовать событие SelectionChange объекта Worksheet.

Во-первых, вы очищаете цвет фона всех ячеек на листе, задавая для свойства ColorIndex значение 0. Затем вы выделяете всю строку и столбец активной ячейки, устанавливая их ColorIndex в порядковый номер нужного цвета.

Настройка кода

Если вы хотите настроить код под свои нужды, вам могут пригодиться эти небольшие советы:

  • В нашем примере кода используются два разных цвета, показанные на рисунке выше: индекс цвета 38 для строки и 24 для столбца. Чтобы изменить цвет подсветки, просто замените их любыми кодами ColorIndex по вашему выбору.
  • Чтобы получить одинаковую окраску строки и столбца, используйте для них один и тот же номер индекса цвета.
  • Чтобы выделить только активную строку, удалите или закомментируйте эту строку: .EntireColumn.Interior.ColorIndex = 24
  • Чтобы выделить только активный столбец, удалите или закомментируйте эту строку: .EntireRow.Interior.ColorIndex = 38

Как добавить код на лист

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

  1. В книге нажмите клавиши ALT + F11, чтобы открыть редактор VBA.
  2. В обозревателе проектов слева вы увидите список всех открытых книг и их рабочих листов. Если вы его не видите, используйте сочетание клавиш Ctrl + R, чтобы отобразить окно Project Explorer.
  3. Найдите целевую книгу. В папке Объекты Microsoft Excel дважды щелкните лист, к которому вы хотите применить выделение. В данном примере это Лист 1.
  4. В окне кода справа вставьте приведенный выше код.
  5. Сохраните файл как книгу с поддержкой макросов (.xlsm).

Преимущества: все делается в бэкенде; никаких настроек/настроек со стороны пользователя не требуется; работает во всех версиях Excel.

Недостатки: есть два существенных недостатка, которые делают этот метод неприменимым при определенных обстоятельствах:

  • Код очищает фоновые цвета всех ячеек на листе. Если у вас есть цветные ячейки, не используйте это решение, потому что ваше пользовательское форматирование будет потеряно.
  • Выполнение этого кода блокирует функцию отмены на листе, и вы не сможете отменить ошибочное действие, нажав Ctrl + Z .

Выделить активную строку и столбец без VBA

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

  1. Выберите набор данных, в котором нужно выделить выделение.
  2. На вкладке Главная в группе Стили нажмите Новое правило.
  3. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек.
  4. В поле Форматировать значения, в которых эта формула верна, введите одну из следующих формул:

Чтобы выделить активную строку:

Чтобы выделить активный столбец:

Чтобы выделить активную строку и столбец:

=ИЛИ(ЯЧЕЙКА("строка")=СТРОКА(), ЯЧЕЙКА("Столбец")= СТОЛБЦ())

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

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

К сожалению, это решение не так хорошо, как решение VBA, поскольку требует пересчета листа вручную (нажатием клавиши F9). По умолчанию Excel пересчитывает рабочий лист только после ввода новых данных или редактирования существующих, но не при изменении выбора. Итак, вы выбираете другую ячейку - ничего не происходит. Нажимаем F9 — лист обновляется, формула пересчитывается, подсветка обновляется.

Чтобы рабочий лист автоматически пересчитывался всякий раз, когда происходит событие SelectionChange, вы можете поместить этот простой код VBA в модуль кода вашего целевого листа, как описано в предыдущем примере:

Код вызывает пересчет выбранного диапазона/ячейки, что, в свою очередь, приводит к обновлению функции ЯЧЕЙКА и отражению изменений в условном форматировании.

Преимущества: в отличие от предыдущего метода, этот не влияет на существующее форматирование, которое вы применили вручную.

Недостатки: может снизить производительность Excel.

  • Чтобы условное форматирование работало, необходимо заставить Excel пересчитывать формулу при каждом изменении выделения (либо вручную с помощью клавиши F9, либо автоматически с помощью VBA). Принудительные пересчеты могут замедлить работу вашего Excel. Поскольку наш код пересчитывает выборку, а не весь лист, негативный эффект, скорее всего, будет заметен только на действительно больших и сложных книгах.
  • Поскольку функция ЯЧЕЙКА доступна в Excel 2007 и более поздних версиях, этот метод не будет работать в более ранних версиях.

Выделить выбранную строку и столбец с помощью условного форматирования и VBA

В случае, если предыдущий метод значительно замедляет вашу рабочую книгу, вы можете подойти к задаче по-другому: вместо пересчета рабочего листа при каждом перемещении пользователя, получите номер активной строки/столбца с помощью VBA, а затем отправьте этот номер функцию ROW() или COLUMN() с помощью формул условного форматирования.

Для этого необходимо выполнить следующие шаги:

  1. Добавьте в книгу новый пустой лист и назовите его Вспомогательный лист. Единственная цель этого листа — хранить два числа, представляющие строку и столбец, содержащие выбранную ячейку, чтобы вы могли безопасно скрыть лист позже.
  2. Вставьте приведенный ниже VBA в окно кода рабочего листа, где вы хотите реализовать подсветку. Подробные инструкции см. в нашем первом примере.

А теперь давайте подробно рассмотрим три основных варианта использования.

Как выделить активную строку

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

=ROW()='Вспомогательный лист'!$A$2

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

Как выделить активный столбец

Чтобы выделить выбранный столбец, введите номер столбца в функцию СТОЛБЦ, используя следующую формулу:

=COLUMN()='Вспомогательный лист'!$B$2

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

Как выделить активную строку и столбец

Чтобы выбранная строка и столбец автоматически окрашивались одним цветом, объедините функции ROW() и COLUMN() в одну формулу:

=OR(ROW()='Вспомогательный лист'!$A$2, COLUMN()='Вспомогательный лист'!$B$2)

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

Преимущества: оптимизированная производительность; работает во всех версиях Excel

Недостатки: самая долгая настройка

Вот как выделить столбец и строку выбранной ячейки в Excel.Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!

Поэтому я решил создать учебник и видео по нему. Это сэкономит мне время и поможет читателям.

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

<Р>