sassriverrat
Well-known Member
- Joined
- Oct 4, 2018
- Messages
- 655
So I the code below should input a timestamp in cell F4 on the sheets within the workbook (except those 3 excluded in the beginning). Well running the sheet, I'm getting an error and it highlights the line in red below. Ideas? Originally "F4" on the activesheet was locked and I set it to be an unlocked cell and that didn't help.
I've also had it error when trying to input data on "R9" cell on a "Noon" sheet. same error code as above. This line is highlighted in Green. Thanks for the help.
I've also had it error when trying to input data on "R9" cell on a "Noon" sheet. same error code as above. This line is highlighted in Green. Thanks for the help.
Rich (BB code):
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
If sh.Name = "Developer" _
Or sh.Name = "Notes" _
Or sh.Name = "Ports" _
Or sh.Name = "Voyage Specifics" _
Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
'If Not Intersect(Target, Union(Range("R5"), Range("W25"))) Is Nothing Then
If Target.Address(0, 0) = "R5" Or Target.Address(0, 0) = "W25" Then
If Cells(25, 23) <> "" Then
Cells(4, 6) = Cells(25, 23).Value
Cells(4, 6).NumberFormat = "dd-mmm-yy"
ElseIf Cells(5, 18) <> "" And Cells(25, 23) = "" Then
Cells(4, 6) = Date
Cells(4, 6).NumberFormat = "dd-mmm-yy"
ElseIf Cells(5, 18) = "" And Cells(6, 23) = "" Then
Cells(4, 6) = "No Data Input"
End If
End If
If sh.Name = "Arrival" Then
If Cells(20, 26) <> "Yes" Then
Range("R6").Select
With Selection
.Locked = False
.ClearContents
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).ColorIndex = 0
.Borders(xlEdgeLeft).TintAndShade = 0
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).ColorIndex = 0
.Borders(xlEdgeTop).TintAndShade = 0
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = 0
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).ColorIndex = 0
.Borders(xlEdgeRight).TintAndShade = 0
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 65535
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
ElseIf Cells(20, 26) <> "No" Then
Cells(6, 18) = "EXACT"
Range("R6").Select
With Selection
.Locked = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).ColorIndex = 0
.Borders(xlEdgeTop).TintAndShade = 0
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = 0
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
Range("R7").Select
End If
ElseIf Cells(20, 26) <> "Yes" Then
Range("R9").Select
With Selection
.Locked = False
.ClearContents
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).ColorIndex = 0
.Borders(xlEdgeLeft).TintAndShade = 0
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).ColorIndex = 0
.Borders(xlEdgeTop).TintAndShade = 0
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = 0
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).ColorIndex = 0
.Borders(xlEdgeRight).TintAndShade = 0
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 65535
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
Range("R6").Select
ElseIf Cells(20, 26) <> "No" Then
Cells(9, 18) = "EXACT"
Range("R9").Select
With Selection
.Locked = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).ColorIndex = 0
.Borders(xlEdgeTop).TintAndShade = 0
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = 0
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
Range("R6").Select
End If
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
