Многие при работе с таблицами любят их окрашивать в различные цвета по "категориям": красный - прогул; синий - больничный; желтый - коммандировка и т.д. и т.п. Это вполне может быть визуально наглядно и красиво, но в последствии хочется по подобной боевой раскраске осуществить с ячейками какие-то иные операции, чем просто полюбоваться. Например: подсчитать количество ячеек определенного цвета, отфильтровать по цвету и т.п. Сегодня я хочу рассказать, как можно защитить ячейки в зависимости от цвета заливки, шрифта. Один из случаев, когда закраска ячеек может быть оправдана - можно каким-то определенным цветом отметить ячейки с формулами, а потом их защитить кодом ниже.
В принципе это можно сделать руками: выделить все ячейки нужного цвета, правая кнопка мыши -Формат ячеек -Защита -Защищаемая ячейка. После чего защитить лист. Подробнее можно прочитать в статье Как разрешить изменять только выбранные ячейки? и в статье Защита листов и ячеек в 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(
Теперь выделяем все ячейки на листе, среди которых есть те, которые надо защитить -нажимаем сочетание клавиш
Защищать ячейки с конкретным цветом получилось, но что делать, если окрашены как раз те ячейки, защищать которые не надо? Да еще и ячейки окрашены разными цветами? Все просто: для защиты выбираем весь диапазон значимых ячеек, а в качестве ячейки-образца с заливкой указываем любую ячейку без заливки. Вот и все. Все ячейки без заливки будут защищены, а с заливкой - разрешены для ввода.
Скачать пример кода в файле:
Защита ячеек по цвету.xls (64,5 КиБ, 1 617 скачиваний)
Так же см.:
Подсчитать сумму ячеек по цвету заливки
Подсчитать сумму ячеек по цвету шрифта
Хит статей по работе с цветными ячейками
