Как в экселе сделать подсветку строки при наведении курсора

Подсветка строки или столбца цветом в Excel для активной ячейки

Время от времени бывает удобно иметь возможность выделить (подсветить) активную строку или столбец. Решение в очень простом методе достижения этого эффекта. Мы будем использовать условное форматирование и лишь пару строк кода VBA.

Подсветка цветом строки активной ячейки в Excel

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

Выберите диапазон ячеек B2:K23 так, чтобы ячейка B2 оставалась активной.

Затем выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для форматируемых ячее». В поле «Форматировать значения, для которых следующая формула является истинной:» введите следующую формулу.

Внимание: Адрес ячейки в левой части формулы B2 должен соответствовать АКТИВНОЙ ЯЧЕЙКИ выбранного диапазона в момент выделения! Посмотрите на рисунок ниже. На нем вы можете видеть, что в выбранной области одна ячейка (если вы ничего не напутали, это будет ячейка в верхнем левом углу диапазона) не окрашена (отсутствует синий фон). Эта ячейка является активной ячейкой в момент выделения диапазона. Адрес именно этой ячейки вы должны использовать при условном форматировании. Как вы можете видеть, в нашем примере это ячейка B2.

Запись СТРОКА(B2) = АктивнаяСтрока означает то, что ячейка выбранного диапазона будет отформатирована, если номер строки этой ячейки равен значению, хранящемуся в созданном имени диапазона АктивнаяСтрока.

То, что сейчас вы должны сделать, так это создать это имя и найти способ присвоить ему номер строки активной ячейки. Для начала создайте новое имя (CTRL+F3) и присвойте ему для начала просто значение ноль.

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

Запустите редактор VBA (Alt+F11) и для листа (Например, «Пример2»), в котором вы хотите подсвечивать любые строки и где создано условное форматирование , введите код:

Private Sub Worksheet_SelectionChange( ByVal Target As Range)
ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Row
End Sub

Если вы бы хотели, чтобы строки подсвечивались цветом только при перемещении в пределах определенного диапазона, вы можете немного изменить код, который мог бы выглядеть так (для нашего Примера 1):

Private Sub Worksheet_SelectionChange( ByVal Target As Range)
If Not Intersect(Target, Range( «B2:K23» )) Is Nothing Then
ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Column
End If
End Sub

Окончательный результат выглядит так:

Туда где перемещается курсор автоматически подсвечивается цветом целая строка диапазона в месте нахождения активной ячейки.

Подсветка цветом столбца в Excel

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

1. В условном форматировании заменить функцию СТРОКА на СТОЛБЕЦ.
2. Изменить в коде VBA-макроса свойство ActiveCell.Row на ActiveCell.Column.

Примечание. Имя исходного диапазона ячеек «АктивнаяСтрока» можно не изменять и оставить прежним как есть.

Решение изменения 1: Выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»:

В появившемся окне «Диспетчер правил условного форматирования» выберите правило и нажмите на кнопку «Изменить правило»:

Изменяем функцию, вносим желаемые стили оформления формата, которые доступны нам по кнопке «Формат»:

И нажимаем ОК на всех открытых окнах.

Решение изменения 2: Открываем редактор VBA-кода и изменяем только лишь одно свойство для объекта ActiveCell с Row на Column:

Наслаждаемся готовым желаемым результатом:

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

Источник

Как в экселе сделать подсветку строки при наведении курсора

Нашёл пример на одном форуме, чуть подправил.
В модуль книги:

В модуль контролируемого (первого в данном случае) листа:

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Option Explicit

Private Sub CheckBox1_Click()
NoEvents = Not CheckBox1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim addr As String
Dim x As Variant
Dim rng, c, r, cll As String

If NoEvents Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
addr = ActiveCell.Address()
x = Split(addr, «$»)

c = x(1)
r = x(2)
rng = c & «:» & c & «,» & r & «:» & r
Range(rng).Select
cll = c & r
Range(cll).Activate
End Sub

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

Нашёл пример на одном форуме, чуть подправил.
В модуль книги:

В модуль контролируемого (первого в данном случае) листа:

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Option Explicit

Private Sub CheckBox1_Click()
NoEvents = Not CheckBox1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim addr As String
Dim x As Variant
Dim rng, c, r, cll As String

If NoEvents Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
addr = ActiveCell.Address()
x = Split(addr, «$»)

c = x(1)
r = x(2)
rng = c & «:» & c & «,» & r & «:» & r
Range(rng).Select
cll = c & r
Range(cll).Activate
End Sub

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

Сообщение Нашёл пример на одном форуме, чуть подправил.
В модуль книги:

В модуль контролируемого (первого в данном случае) листа:

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Option Explicit

Private Sub CheckBox1_Click()
NoEvents = Not CheckBox1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim addr As String
Dim x As Variant
Dim rng, c, r, cll As String

If NoEvents Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
addr = ActiveCell.Address()
x = Split(addr, «$»)

c = x(1)
r = x(2)
rng = c & «:» & c & «,» & r & «:» & r
Range(rng).Select
cll = c & r
Range(cll).Activate
End Sub

На листе расположен чекбокс, включающий весь механизм.
Будут подсвечиваться строки при изменении выделения ячейки. Автор — Hugo
Дата добавления — 28.02.2014 в 13:29

Источник

Координатное выделение

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ — со своими плюсами и минусами. Давайте разберем их детально.

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой — именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off — выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

• относительная простота реализации
• выделение — операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

• такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки — выделяются сразу все строки и столбцы, входящие в объединение
• если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA — высший пилотаж 😉

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке — высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

• кодовое слово для параметра, например «столбец» или «строка»
• адрес ячейки, для которой мы хотим определить значение этого параметра

Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.

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

1. Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
2. В Excel 2003 и более старших версиях открываем меню Формат — Условное форматирование — Формула (Format — Conditional Formatting — Formula) . В Excel 2007 и новее — жмем на вкладке Главная (Home) кнопку Условное форматирование — Создать правило (Conditional Formatting — Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
3. Вводим формулу для нашего координатного выделения:

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

Нажмите кнопку Формат (Format) и задайте цвет заливки.

Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

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

• Условное форматирование не нарушает пользовательское форматирование таблицы
• Этот вариант выделения корректно работает с объединенными ячейками.
• Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
• Макросы используются минимально

• Формулу для условного форматирования надо вводить вручную.
• Нет быстрого способа включить-выключить такое форматирование — оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

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

• в Excel 2003 и старше — через меню Сервис — Надстройки — Обзор (Tools — Add-Ins — Browse)
• в Excel 2007 и новее — через Файл — Параметры — Надстройки — Перейти — Обзор (File — Excel Options — Add-Ins — Go to — Browse)

Источник