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

В этом уроке вы узнаете 3 различных способа динамического выделения строки и столбца выбранной ячейки в Excel.

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

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

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

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Если Target.Cells.Count > 1, то выйдите из Sub Application.ScreenUpdating = False ‘Очистить цвет всех ячеек Cells.Interior.ColorIndex = 0 With Target ‘Выделите строку и столбец выбранной ячейки .EntireRow.Interior.ColorIndex = 38 .EntireColumn.Interior.ColorIndex = 24 End With Application.ScreenUpdating = True End Sub

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

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

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

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

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

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

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

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

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

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

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

Выберите свой набор данных, в котором нужно сделать выделение.
На Дом вкладка, в Стили группа, нажмите Новое правило.
в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
в Форматировать значения, где эта формула верна введите одну из следующих формул:
Чтобы выделить активную строку:
=ЯЧЕЙКА(“строка”)=СТРОКА()
Чтобы выделить активный столбец:
=ЯЧЕЙКА(“столбец”)=СТОЛБЦ()
Чтобы выделить активную строку и столбец:
=ИЛИ(ЯЧЕЙКА(“строка”)=СТРОКА(), ЯЧЕЙКА(“столбец”)= СТОЛБЦ())
Все формулы используют функцию CELL для возврата номера строки/столбца выбранной ячейки.
Нажмите на Формат кнопку, переключитесь на Наполнять вкладку и выберите нужный цвет.
Нажмите OK дважды, чтобы закрыть оба диалоговых окна.

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

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

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

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

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Target.Calculate End Sub

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

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

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

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

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

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

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

Добавьте новый пустой лист в книгу и назовите его Вспомогательный лист. Единственная цель этого листа — хранить два числа, представляющие строку и столбец, содержащие выбранную ячейку, поэтому вы можете безопасно скрыть лист позже.
Вставьте приведенный ниже VBA в окно кода рабочего листа, где вы хотите реализовать подсветку. Для получения подробных инструкций, пожалуйста, обратитесь к нашим первый пример.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False Worksheets(“Вспомогательный лист”).Cells(2, 1) = Target.Row Worksheets(“Вспомогательный лист”).Cells(2, 2) = Target.Column Application.ScreenUpdating = True End Sub

Приведенный выше код помещает координаты активной строки и столбца на лист с именем «Вспомогательный лист». Если вы назвали свой лист по-другому на шаге 1, измените имя рабочего листа в коде соответствующим образом. Номер строки записывается в A2, а номер столбца в B2.
На целевом листе выберите весь набор данных и создайте правило условного форматирования с приведенными ниже формулами. Пошаговое руководство представлено в приведенном выше примере.

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

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

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

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

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

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

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

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

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

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

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

=ИЛИ(СТРОКА()=’Вспомогательный лист’!$A$2, COLUMN()=’Вспомогательный лист’!$B$2)

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

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

Недостатки: самая длинная установка

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

Практическая рабочая тетрадь для скачивания

Выделение активной строки и столбца (файл .xlsm)

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