Selected Answer
Your code can't work because it loops through all the cells A1:H214. You want it to stop looping when it finds TODAY(). The command to use is Exit For. In the same breath, you don't need to select the cell where the date was found if you wish to select the cell below it. Here is a corrected version of your code. Except for the protection bits it is tested and works.
Private Sub Workbook_Open()
Worksheets("view only").Activate
ActiveSheet.Unprotect Password:="holidays"
For Each Cell In ActiveSheet.Range("A1:H214")
If Cell.Value = [Today()] Then
Cell.Offset(1).Select
Exit For
End If
Next Cell
ActiveSheet.Protect Password:="holidays", Structure:=True, Windows:=True
End Sub
The thing that hurts my eye when looking at your code is that the variable isn't declared. That is the source of millions of baffled beginner hours and tons of their pulled hair. It is wrongly claimed that adding Option Explicit at the top of your code will "force" you to declare variables. The better truth is that it will help you find undeclared variables. If you always declare your variables undeclared variables won't give you a headache. The code below corrects that omission.
I also found it useful to know when I last edited a procedure. Therefore I add the date. Finally, code becomes easier to read if you start by saying what you intend to do next. For this purpose the With statement is useful. All your code is about Worksheets("view only"). So you introduce it at the start, add End With at the end and precede every reference to it with a period which takes the place of its full name. Below is your exact code, beautified to my preference. I hope you like it too.
Private Sub Workbook_Open()
' 25 Sep 2018
Dim Cell As Range
With Worksheets("view only")
.Activate
.Unprotect Password:="holidays"
For Each Cell In .Range("A1:H214")
If Cell.Value = [Today()] Then
Cell.Offset(1).Select
Exit For
End If
Next Cell
.Protect Password:="holidays", Structure:=True, Windows:=True
End With
End Sub
Observe the indenting which is very helpful for reading code. Reading code is important when trouble-shooting. The more readable your code is the less time you will spend on searching for coding errors. With better indenting you might have found the missing loop exit yourself. Most definitely you would have seens that the sheet protection must be applied after the looping is done and irrespective of whether TODAY() was found or not. BTW, workbooks have Sheets and Worksheets. Every Worksheet is a Sheet but not every Sheet is a Worksheet.
Your code potentially loops through 1712 cells to find TODAY(). Excel can do that a couple of hundred times faster than VBA. Use VBA's Find method to harness Excel to do this job. The code below does that.
Private Sub Workbook_Open()
' 25 Sep 2018
Dim Rng As Range
With Worksheets("view only")
Set Rng = .Range("A1:H214")
.Activate
.Unprotect Password:="holidays"
On Error Resume Next
Rng.Find("=TODAY()").Offset(1).Select
If Err Then Rng.Find(CDate(Format(Date, "Short Date"))).Offset(1).Select
.Protect Password:="holidays", Structure:=True, Windows:=True
End With
End Sub
In this procedure the range to be searched is declared because it is used twice. After declaring it, Rng.Find is exactly the same as .Range("A1:H214").Find. Since the range includes a hard-coded address you don't want it in the code more than once, in case it ever needs to be changed.
Rng.Find("=TODAY()") finds the cell containing the date and Rng.Find("=TODAY()").Offset(1).Select selects the cell below it, all in one line of code. But what if it isn't found? The preceding line On Error Resume Next caters to this eventuality. If there is an error, meaning TODAY() wasn't found, the code makes a second attempt. It now looks for "25/09/2018". Using Find with dates is a little complicated. Since this post is already long enough, suffice it to say that I included this line to enable you to look for a hard-entered date if the need should arise. Date is VBA's equivalent for Excel's TODAY(). You can replace "Date" with any other date in this formula, Rng.Find(CDate(Format(Date, "Short Date")))
If the Find attempts fail nothing will be selected. That is the result of all three variations of your code in this post, including the corrected version of your original procedure.
