Excel vba выделить ячейки по условию

Обновлено: 07.07.2024

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

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

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

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

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

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

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

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

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

Плюсы этого способа:

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

Минусы этого способа:

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

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

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

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

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

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

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

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

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

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

Плюсы этого способа:

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

Минусы этого способа:

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

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

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

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

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

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

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

В таблице отчета необходимо выделить те ячейки, которые содержат процентные значения показателей по отделам на уровне 100% или больше. Для решения данной задачи можно использовать условное форматирования. Но лучше написать свой макрос, который возможно будет хорошим началом для многофункциональной программы. Ведь с помощью макроса мы получим доступ к значениям интересующих нас ячеек, с которыми потом можно будет выполнять различные вычислительные операции и т.п. Такой возможности условное форматирование не предоставляет.

Как выделить ячейки макросом по условию больше или равно

Исходная таблица отчета выглядит следующим образом:

Откройте редактор Visual Basic (ALT+F11) и вставьте новый модуль используя инструмент: «Вставка»-«Модуль». А после чего введите в него следующий код макроса:

Sub BolsheRavno()
Dim i As Long
Dim znach As Variant
Dim diapaz1 As Range
Dim diapaz2 As Range
znach = InputBox( "Введите минимальное число для выделения ячеек" )
If znach = "" Then Exit Sub
If IsNumeric(znach) Then
znach = znach * 1
Else
MsgBox "Допустимо вводить только числовые значения!"
Exit Sub
End If
Set diapaz1 = Application.Intersect(Selection, ActiveSheet.UsedRange)
If diapaz1 Is Nothing Then
MsgBox "Сначала выделите диапазон!"
Exit Sub
Else
For i = 1 To diapaz1.Count
If diapaz1(i) >= znach And IsNumeric(diapaz1(i)) _
And Not IsEmpty(diapaz1(i)) Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1(i)
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
End If
End If
Next
End If
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
diapaz2. Select
MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub

Теперь, если мы хотим автоматически выделить несмежный диапазон ячеек, которые содержат в своем значении 100% или больше, выделите диапазон B2:F10. А потом выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«BolsheRavno»-«Выполнить». В результате чего появиться диалоговое окно интерфейса нашего макроса.

В поле ввода введите числове значение 1 и нажмите на кнопку ОК:

Макрос выделит все ячейки в предварительно выделенном диапазоне данных таблицы, значения которых находиться на уровне 100% или больше. После выполнения кода макроса можно изменить вон выделенных ячеек выбрав инструмент: «ГЛАВНАЯ»-«Шрифт»-«Цвет заливки».

Заполняя поле ввода диалогового окна макроса следует учитывать, что исходное вводимое значение должно быть числом. А если нас интересуют другие значения кроме 100% тогда следует учитывать формат ячеек. Например, для 50% следует вводить 0,5. Все должно быть в соответствии с форматом отображения чисел в ячейках.

В данном VBA-коде выполняется 5 проверок для инструкций алгоритма и введенного значения в диалоговое окно интерфейса макроса:

If znach = "" Then Exit Sub

If IsNumeric(znach) Then

If diapaz1 Is Nothing Then

If diapaz1(i) >= znach

If diapaz2 Is Nothing Then

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

Макрос для выделения ячеек по условию меньше или равно

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

Скопируйте и вставьте в этот же модуль исходный код «BolsheRavno».
Переименуйте название для копии кода в макросе с «BolsheRavno» на «MensheRavno».

znach = InputBox("Введите максимальное число для выделения ячеек")

If diapaz1(i) <= znach

Полная версия кода для MensheRavno выглядит следующим образом:

Sub MensheRavno()
Dim i As Long
Dim znach As Variant
Dim diapaz1 As Range
Dim diapaz2 As Range
znach = InputBox( "Введите максимальное число для выделения ячеек" )
If znach = "" Then Exit Sub
If IsNumeric(znach) Then
znach = znach * 1
Else
MsgBox "Допустимо вводить только числовые значения!"
Exit Sub
End If
Set diapaz1 = Application.Intersect(Selection, ActiveSheet.UsedRange)
If diapaz1 Is Nothing Then
MsgBox "Сначала выделите диапазон!"
Exit Sub
Else
For i = 1 To diapaz1.Count
If diapaz1(i) <= znach And IsNumeric(diapaz1(i)) _
And Not IsEmpty(diapaz1(i)) Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1(i)
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
End If
End If
Next
End If
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
diapaz2. Select
MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub

Чтобы проверить в действии работу второго макроса, снова выделите диапазон ячеек B2:F10 и выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«MensheRavno»-«Выполнить».

На этот раз в появившиеся диалоговое окно для поиска меньших значений от исходного вводим значение 0,5 и нажимаем ОК:

В результате мы видим, что макрос «MensheRavno» функционирует обратно пропорционально макросу «BolsheRavno».

В Microsoft Excel обычно выбирается ячейка или ячейки, а затем выполняется действие, например форматирование ячеек или ввод значений. В Visual Basic обычно не требуется выбирать ячейки перед их изменением.

Например, чтобы ввести формулу в ячейку D6 с помощью Visual Basic, не нужно выбирать диапазон D6. Просто возвратите объект Range для этой ячейки, а затем присвойте свойству Formula нужную формулу, как показано в следующем примере.

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

Использование метода Select и свойства Selection

Метод Select активирует листы и объекты на листах; свойство Selection возвращает объект, представляющий текущее выделение на активном листе в активной книге. Перед использованием свойства Selection необходимо активировать книгу, активировать или выбрать лист, а затем выбрать диапазон (или другой объект) с помощью метода Select.

Средство записи макросов часто создает макрос, использующий метод Select и свойство Selection. Следующая процедура Sub была создана с помощью средства записи макросов и показывает, как Select и Selection работают вместе.

В следующем примере выполняется та же задача без активации или выбора листа или ячеек.

Выбор ячеек на активном листе

Если для выбора ячеек используется метод Select, имейте в виду, что Select работает только на активном листе. Если выполнить процедуру Sub из модуля, метод Select завершится ошибкой, если процедура не активирует лист перед использованием метода Select для диапазона ячеек. Например, следующая процедура копирует строку из Листа1 в Лист2 в активной книге.

Активация ячейки в выделенном фрагменте

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

Поддержка и обратная связь

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

Макрос заливки всей строки по условию в ячейке
Здравствуйте! Есть таблица: Пытаюсь написать макрос который должен закрашивать всю строку.

Копирование всей строки по условию одной из ячеек
Доброго времени суток. Имею таблицу со списком людей. Нужно определить людей конкретного года.

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

Выделение в ListView всей строки
Есть список с колонками. Каждый элемент списка (ряд) представлен несколькими контролами.

eQyes, вы это тоже можете, включив запись макроса в меню СЕРВИС (2003) или Разработчик(2007). Затем делаете вручную, нужные вам действия. Затем ОСТАНОВИТЬ ЗАПИСЬ и в макросах смотрите, сформированный экселем макрос. Пользуйтесь этим почаще быстрее научитесь делать такие простые вещи самостоятельно. А пока получите макрос. Считаем, что "недвижимость" в первом столбце.

Burk, на значительных объемах применение массивов даст существенную прибавку в скорости
МВТ, На значительном количестве ячеек, содержащих искомый текст, такой подход приведёт к возникновению ошибки, ибо Len(s) не должна превышать 255 символов. pashulka, да, что-то не подумал . Спасибо за замечание. Как вариант, можно в коллекцию строки собирать, все равно должно быть значительно быстрее, чем поячеечно перебирать данные или даже использовать Union

Если из 40000 - 10000 ячеек будут с недвижимостью, то тут Union не самый лучший вариант, а если 100, то можно его и помучить

P.S. Кстати, при переборе ячеек не лишним будет использовать :

Добавлено через 1 час 21 минуту
Благодаря замечаниям уважаемого pashulka были выявлены несколько досадных неточностей в коде. Прикладываю исправленный вариант
а просто макрос записать макрорекордером не пробовали
Господа, вы рассуждаете о такой пустяковой задаче, как о проблеме века. Судя по всему, автор темы мало чего знает о VBA и, когда я пишу ответы на вопрос, то стараюсь наметить возможные пути (далеко не все) для решения задачи и достаточно простые, надеясь, что это станет толчком к самостоятельным действиям автора. Я думаю, что сейчас он оценивает ваши алгоритмы с точки зрения "работает-не работает". А у вас этот "пустячок" как зеркало, чтобы полюбоваться на себя. Алгоритмы мне понравились, надеюсь, что и автор темы в них разберётся. С наилучшими пожеланиями. Burk
P.S. Вскоре создам новую, более серьёзную тему, если вам известно её решение В НУЖНОМ МНЕ АСПЕКТЕ буду признателен.

Выделение всей строки в контроле TextBox
Представляю на ваш суд метод выделения в текстбоксе строки, в которой находится курсор, целиком и.

Реализовать выделение всей строки MSFlexGrid
Здравствуйте! Может кто нибудь подскажет как в MSFlexGrid сделать так, что бы строка выделялась.

Выделение всей строки синим цветом в StringGrid
Здравствуйте. Как сделать выделение всей строки синим цветом в StringGrid Есть свойство.

Читайте также: