Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Защита ячеек в зависимости от цвета

Многие при работе с таблицами любят их окрашивать в различные цвета по "категориям": красный - прогул; синий - больничный; желтый - коммандировка и т.д. и т.п. Это вполне может быть визуально наглядно и красиво, но в последствии хочется по подобной боевой раскраске осуществить с ячейками какие-то иные операции, чем просто полюбоваться. Например: подсчитать количество ячеек определенного цвета, отфильтровать по цвету и т.п. Сегодня я хочу рассказать, как можно защитить ячейки в зависимости от цвета заливки, шрифта. Один из случаев, когда закраска ячеек может быть оправдана - можно каким-то определенным цветом отметить ячейки с формулами, а потом их защитить кодом ниже.
В принципе это можно сделать руками: выделить все ячейки нужного цвета, правая кнопка мыши -Формат ячеек -Защита -Защищаемая ячейка. После чего защитить лист. Подробнее можно прочитать в статье Как разрешить изменять только выбранные ячейки? и в статье Защита листов и ячеек в MS Excel. Но если ячеек много, да еще они разбросаны по листу, то такой метод довольно утомителен. Однако это очень просто сделать при помощи кода ниже:

'---------------------------------------------------------------------------------------
' Module    : mLockCells
' DateTime  : 15.04.2014 12:18
' Author    : The_Prist(Щербаков Дмитрий)
'             WebMoney - R298726502453; Яндекс.Деньги - 41001332272872
'             http://www.excel-vba.ru
' Purpose   : http://www.excel-vba.ru/chto-umeet-excel/zashhita-yacheek-v-zavisimosti-ot-cveta/
'---------------------------------------------------------------------------------------
 
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : PrCellsByInterior
' Purpose   : Вызываем процедуру защиты ячеек в зависимости от цвета заливки
'---------------------------------------------------------------------------------------
Sub PrCellsByInterior()
    Call Protect_Cells(True)
End Sub
'---------------------------------------------------------------------------------------
' Procedure : PrCellsByFont
' Purpose   : Вызываем процедуру защиты ячеек в зависимости от цвета шрифта
'---------------------------------------------------------------------------------------
Sub PrCellsByFont()
    Call Protect_Cells(False)
End Sub
 
'---------------------------------------------------------------------------------------
' Procedure : Protect_Cells
' Purpose   : Основная процедура защиты ячеек
'             Не учитываем ячейки с цветом, установленным при помощи Условного форматирования
'---------------------------------------------------------------------------------------
Sub Protect_Cells(bInterior As Boolean)
    Dim rCell As Range, rRange As Range, rCr As Range
    Dim bLock As Boolean
 
    ActiveSheet.Unprotect "1234"
    On Error Resume Next
    'указываем диапазон ячеек, в которых изменяем защиту ячеек.
    'если выбран диапазон A1:B10 - ячейки с выбранным цветом будут защищены только в этом диапазоне.
    Set rRange = Application.InputBox("Выберите диапазон ячеек для изменения атрибута Защищаемая ячейка", "www.excel-vba.ru", Type:=8)
    If rRange Is Nothing Then MsgBox "Неверный диапазон", vbCritical, "Ошибка": Exit Sub
    'указываем ячейку-образец с цветом заливки/шрифта.
    'именно ячейки этого цвета будут защищены(или наоборот - только в них будет разрешен ввод)
    Set rCr = Application.InputBox("Выберите ячейку с образцом цвета", "www.excel-vba.ru", Type:=8)
    If rRange Is Nothing Then MsgBox "Неверный диапазон", vbCritical, "Ошибка": Exit Sub
    'определяем, защитить ячейки выбранного цвета или наоборот - рарешить ввод только в них
    bLock = (MsgBox("Да - Защитить ячейки указанного цвета;" & vbNewLine & "Нет - Разрешить ввод только в ячейки указанного цвета.", vbQuestion + vbYesNo, "www.excel-vba.ru") = vbYes)
    'меняем параметры защиты всех остальных ячеек
    rRange.Locked = Not bLock
    'применяем параметры защиты к ячейкам
    For Each rCell In rRange
        If bInterior Then    'по цвету заливки
            If rCell.Interior.Color = rCr.Interior.Color Then rCell.Locked = bLock
        Else    'по цвету шрифта
            If rCell.Font.Color = rCr.Font.Color Then rCell.Locked = bLock
        End If
    Next rCell
    'защищаем лист, установив пароль 1234
    ActiveSheet.Protect "1234"
End Sub

Как использовать код в своей книге: копируем код выше, в нужной книге переходим в редактор VBA(Alt+F11) -Insert -Module. В появившееся окно вставляем скопированный код.
Теперь выделяем все ячейки на листе, среди которых есть те, которые надо защитить -нажимаем сочетание клавиш Alt+F8 и выбираем из списка PrCellsByInterior, если надо защищать ячейки на основании цвета заливки или PrCellsByFont, если защищать надо в зависимости от цвета шрифта.
Защищать ячейки с конкретным цветом получилось, но что делать, если окрашены как раз те ячейки, защищать которые не надо? Да еще и ячейки окрашены разными цветами? Все просто: для защиты выбираем весь диапазон значимых ячеек, а в качестве ячейки-образца с заливкой указываем любую ячейку без заливки. Вот и все. Все ячейки без заливки будут защищены, а с заливкой - разрешены для ввода.

Скачать пример кода в файле:

  Tips_Macro_LockCellByColor.xls (48,0 KiB, 940 скачиваний)

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

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


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти