Подсветка незащищенных ячеек
Если вы когда-нибудь использовали защиту ячеек на листе (вкладка Рецензирование - Защитить лист или в старых версиях Excel - меню Сервис - Защита - Защитить лист), то, возможно, сталкивались с этой проблемой. Как известно, будет данная конкретная ячейка на листе защищена от изменений после включения защиты листа, или нет - определяется галочкой Защищаемая ячейка (Locked) в диалоговом окне Формат ячейки (Format Cells) на вкладке Защита (Protection):
В случае применения защиты листа к большой и сложной таблице или экранной форме, где должно быть много областей ввода, не всегда понятно - у каких ячеек на листе эта галочка уже выключена, а у каких еще осталась включена? Опубликованные ниже макросы как раз и позволяют включить/выключить подсветку цветом для незащищенных ячеек на текущем листе, чтобы их было наглядно видно.
Для добавления этих макросов в текущую книгу:
- нажмите сочетание клавиш ALT+F11, чтобы открыть редактор макросов Visual Basic
- вставьте новый пустой модуль в книгу, используя команду меню Insert - Module
- скопируйте и вставьте туда код приведенных ниже макросов
Public Fills
Sub Unprotected_Cells_Show()
Application.ScreenUpdating = False
ReDim Fills(1 To ActiveSheet.UsedRange.Rows.Count, 1 To ActiveSheet.UsedRange.Columns.Count)
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = -4142 Then
Fills(cell.Row, cell.Column) = 0
Else
Fills(cell.Row, cell.Column) = cell.Interior.Color
End If
If Not cell.Locked Then cell.Interior.ColorIndex = 3
Next cell
Application.ScreenUpdating = True
End Sub
Sub Unprotected_Cells_Hide()
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange
If Fills(cell.Row, cell.Column) = 0 Then
cell.Interior.ColorIndex = -4142
Else
cell.Interior.Color = Fills(cell.Row, cell.Column)
End If
Next cell
Application.ScreenUpdating = True
End Sub
Как легко догадаться, первый из этих двух макросов включает подсветку красным для незащищенных ячеек, а второй - выключает ее, восстанавливая исходный цвет заливки. Запустить эти макросы можно, нажав сочетание клавиш ALT+F8 или воспользовавшись кнопкой Макросы (Macros) на вкладке Разработчик (Developer).
Работа макроса на примере экранной формы выглядит примерно так:
И не надо ломать голову и проверять - где ты снял защиту с ячеек, а где она осталась.
Ссылки по теме
- Как защитить отдельные ячейки на листе, весь лист, зашифровать книгу Excel?
- Что такое макросы, как ими пользоваться?
У вас же, возможно, УЖЕ стоит защита листа - снимите ее сначала.
Т.е. применил 1 раз показ не защищенных(их залило красным), потом применил еще раз показ(по запарке), после применяю скрыть(заливка должна пропасть) и ничего. Все так же и остается залитым красным.
Защита листа отключена, версия excell 2010
В дальнейшем же не используется?
по строчке: Fills(cell.Row, cell.Column) = 0
Спасибо!
при том что на одном компе имеется два экселя и в одном работает этот макрос а в другом нет. Я так и не понял в чем была проблема(в кодах не шарю), но применив ваш вариант получилось использовать данную возможность.
Единственный нюанс: по вашему макросу он убирает цвет заливки полность по команде hide , в отличие от варианта автора.
Еще попробовал вариант из сообщения, второй вариант, после этого текста:
И тот кто его заполняет видел эти незащищенные ячейки, те чтобы они были выделены цветом, но при печати этот цвет чтобы не печатался.
Просто есть лист, которые печатаются и нужно видеть какие ячейки заполнять.
У меня такая ситуация: данный макрос работает просто отлично на 9 листах из 10 (как пример), но на 10-ом листе данный макрос (показать незащищенные ячейки) отказывается работать выдает ошибку (Run-time error 9: subscript out of range) и ругается на эту строчку Fills(cell.Row, cell.Column) = cell.Interior.Color. В чем может быть причина?
Справочно: лист не защищен, на листе есть ниспадающий список созданный через Данные - Проверка данных - Список. Также есть условное форматирование, но опытным путем установлено, что данный макрос работает с условным форматированием на других листах.
Какой библиотеки не хватает в 13 excell ?
Процедура не запускается.
Жалуется на For Each celll In ActiveSheet.UsedRange
и еще вопрос: как сделать что б макрос перебирал все листы книги и делал данные процедуры на всех листах?
спасибо.
Скопировал полностью в чистый файл .xlsb, лист не защищен, спецом пару ячеек незащищенных сделал.
В общем сделал все по инструкции в новом тестовом файле.
Выдает ошибку: Fills(cell.Row, cell.Column) = 0
Если удалить Public Fills, то ошибки "Fills(cell.Row, cell.Column) = 0" уже нет, страничку раскрасит как должно.
Но скрытие не работает выделяет ошибку: Can't execute in breake mode и желтым выделена строчку: Sub Unprotected_Cells_Hide()
Собственно мне из макроса нужно только выделение всех незащищенных ячеек, чтобы их потом скопировать на отдельный лист(архив) в те же самые места, чтобы потом другим макросом можно было вернуть все ячейки на место.
Пока не знаю, как это реализовать)
Защиту листов выключил
Модуль положил в "VBAProject (PERSONAL>XLSB)"
Но есть второй вопрос.
У меня в таблице используются ячейки с красными цветами, как поменять в макросе отображение с красного на например голубой?
Заметил такую вещь, если запустить макрос проверки защищённых ячеек и перейти на другой лист, так же проверить защищаемые ячейки и снова перейти на предыдущий лист, то снять выделение защищаемых ячеек не получится - макрос не срабатывает.
Dim cel As Range
Dim Rng As Range
Set Rng = Nothing
On Error GoTo Pipec
For Each cel In ActiveSheet.UsedRange
If cel.Locked = False Then
If Rng Is Nothing Then
Set Rng = cel
Else
Set Rng = Union(Rng, cel)
End If
End If
Next
If Rng Is Nothing Then
MsgBox "НЕзаблокированных ячеек" & vbCrLf & "НЕ НАЙДЕНО.", 64, "В Н И М А Н И Е !"
Else
Rng.Select
End If
Exit Sub
Pipec:
MsgBox "Слишком большой диапазон." & vbCrLf & "Выделение ВСЕХ НЕзаблокированных ячеек невозможно.", 16, "В Н И М А Н И Е !"
End Sub