by using Data Validation, I can prevent any data entry
in selected cells, without the use of sheet protection
Not so. Data Validation is entirely unsecure, you can copy & paste over it from another sheet or another workbook.
gsouza's method works, albeit rather aggressively ; it prevents cells from being selected, rather than just changed.
In the past I've used a "two-handed" approach to this problem:
(i) Worksheet_SelectionChange code to store the value of the selection *somewhere* if it's in the restricted range
(ii) Worksheet_Change code to immediately *re*store the value of a changed cell in the restricted range.
Thus, (i) =
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then
Range("I1") = Target.Value
End If
End Sub
and (ii) =
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then
Application.EnableEvents = False
Target.Value = Range("I1")
Application.EnableEvents = True
MsgBox "Changes to cell " & Target.Address & " aren't allowed."
End If
End Sub
However, like most of my methods it's uber-sloppy. I'm sure regulars here will come up with something far more robust & efficient.
HTH,
Andy