Re: Please Help!!!! If statement, lock and unlock cells

Hi Norman,

Thanks, again. Now it works.

I'm glad that you are very help and thanks for sharing with this. Much
appreciated.

I did not like pop-up message:
"To modify a protected cell or chart, first remove protection using the
Unprotect Sheet command (Tools menu, Protection submenu). You may be
prompted for a password."

Is there a way to create my own message replace this message above ??

Thanks

**********************************
Hi Bill

> One last one, when I enter the data on yellow cell box and I try to make
> it into bold font for only blank cell.

Try this version:

'===================>>
Public Sub Tester02E()
Dim Rng As Range
Dim RngCol As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim arr As Variant
Const PWORD As String = "YOUR PASSWORD"

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE

arr = Array(2, 4, 20, 23, 30, 31) '<<========== CHANGE

With SH
.Unprotect PWORD
.Cells.Locked = True

Application.ScreenUpdating = False

With .Cells
.Interior.ColorIndex = xlNone
.Font.Bold = False
End With

For i = LBound(arr) To UBound(arr)
On Error Resume Next
Set Rng = .Columns(arr(i)).SpecialCells(xlBlanks)
On Error GoTo 0

If Not Rng Is Nothing Then
With Rng.Cells
.Locked = False
.Interior.ColorIndex = 48 '<<===== CHANGE
.Font.Bold = True
End With
End If
Next i
.EnableAutoFilter = True
.Protect PWORD, UserInterfaceOnly:=True

End With

End Sub
'<<===================

> Thanks again. I tried and I couldn't make it work with filter on first
> row. I might have done wrong but I will try to figure it out.

I added a multi-column autofilter range on my test sheet and, after running
the previously suggested code (or the current version), I was able to
interrogate the autofilter on the protected sheet without any hindrance.

---
Regards,
Norman



"Bill" <a@a.com> wrote in message news:4_udnTYTrbOeRajeRVn-uQ@rogers.com...
> Hi Norman,
>
> Thanks again. I tried and I couldn't make it work with filter on first
> row. I might have done wrong but I will try to figure it out.
>
> One last one, when I enter the data on yellow cell box and I try to make
> it into bold font for only blank cell.
>
> Thanks
>

Hi Bill,

> I did not like pop-up message:
> "To modify a protected cell or chart, first remove protection using the
> Unprotect Sheet command (Tools menu, Protection submenu). You may be
> prompted for a password."
>
> Is there a way to create my own message replace this message above ??

No, I do not believe that it is possible to remove or change the default
message

---
Regards,
Norman



"Bill" <a@a.com> wrote in message news:99Wdnaep3KXbG6veRVn-tw@rogers.com...
> Hi Norman,
>
> Thanks, again. Now it works.
>
> I'm glad that you are very help and thanks for sharing with this. Much
> appreciated.
>
> I did not like pop-up message:
> "To modify a protected cell or chart, first remove protection using the
> Unprotect Sheet command (Tools menu, Protection submenu). You may be
> prompted for a password."
>
> Is there a way to create my own message replace this message above ??
>
> Thanks