Многие при работе с таблицами любят их окрашивать в различные цвета по "категориям": красный - прогул; синий - больничный; желтый - коммандировка и т.д. и т.п. Это вполне может быть визуально наглядно и красиво, но в последствии хочется по подобной боевой раскраске осуществить с ячейками какие-то иные операции, чем просто полюбоваться. Например: подсчитать количество ячеек определенного цвета, отфильтровать по цвету и т.п. Сегодня я хочу рассказать, как можно защитить ячейки в зависимости от цвета заливки, шрифта. Один из случаев, когда закраска ячеек может быть оправдана - можно каким-то определенным цветом отметить ячейки с формулами, а потом их защитить кодом ниже.
В принципе это можно сделать руками: выделить все ячейки нужного цвета, правая кнопка мыши -Формат ячеек -Защита -Защищаемая ячейка. После чего защитить лист. Подробнее можно прочитать в статье Как разрешить изменять только выбранные ячейки? и в статье Защита листов и ячеек в 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.MergeArea.Locked = bLock
        Else    'по цвету шрифта
            If rCell.Font.Color = rCr.Font.Color Then rCell.MergeArea.Locked = bLock
        End If
    Next rCell
    'защищаем лист, установив пароль 1234
    ActiveSheet.Protect "1234"
End Sub

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

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

  Защита ячеек по цвету.xls (64,5 КиБ, 1 564 скачиваний)

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

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

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.