Hello,
I need some help with the below code. I know its pretty messy, but hopefully someone can help me clean it up and get it working. The first portion works. The code is assigned to a checkbox. If the checkbox is ticked my activesheet (Status of Closing) range D5 value changes to "Closed" and it will update my Tracker Sheet for that particular loan number and update that status to Closed as well so the two statuses match. (my loan number on my "Status of Closing" sheet is located in cell D4 and a matching loan number will be on my "Tracker" Sheet in column A, however the row varies for each loan number as there is a running list of several that other users can enter)
Second part, is not working. If the status of this loan changes to "Closed" I need my code to find the matching loan number on sheet "Tracker" (which is found in the first section as it updates the status for that particular loan.) and take the row and paste it to the "Closed Loans" Sheet and delete that row from the Tracker Sheet were it was originally copied from. I appreciate any help on this! Thank you!
[/I][/B]I need some help with the below code. I know its pretty messy, but hopefully someone can help me clean it up and get it working. The first portion works. The code is assigned to a checkbox. If the checkbox is ticked my activesheet (Status of Closing) range D5 value changes to "Closed" and it will update my Tracker Sheet for that particular loan number and update that status to Closed as well so the two statuses match. (my loan number on my "Status of Closing" sheet is located in cell D4 and a matching loan number will be on my "Tracker" Sheet in column A, however the row varies for each loan number as there is a running list of several that other users can enter)
Second part, is not working. If the status of this loan changes to "Closed" I need my code to find the matching loan number on sheet "Tracker" (which is found in the first section as it updates the status for that particular loan.) and take the row and paste it to the "Closed Loans" Sheet and delete that row from the Tracker Sheet were it was originally copied from. I appreciate any help on this! Thank you!
Code:
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Sub Loan_Closed()
Dim ws As Worksheet
Dim xlrange As Range, xlrange1 As Range, Rng As Range
Dim valuetofind As String, valuetofind1 As String
Dim ans As Long
Dim Lastrow As Long
Dim answer As String
Dim MyNote As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set ws = Sheets("Status of Closing")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Application.EnableEvents = False
If ws.CheckBoxes("Check Box 406").Value = 1 Then
ws.Unprotect Password:="GoTeam!"
ws.Range("Y57").Value = Date
ws.Range("D5").Value = "Closed"
ws.Protect Password:= "[LEFT][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"></strike>
valuetofind = ws.Range("D3").Value
Set xlrange = Worksheets("Tracker").Range("A2:A200")[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]For Each cell In xlrange
If cell.Value = valuetofind Then
cell.Offset(0, 6).Value = ws.Range("D5").Value
cell.Offset(0, 7).Value = ws.Range("Y57").Value
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Next[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]ws.Protect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
Else
ws.Unprotect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
ws.Range("R57") = ""
ws.Protect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
End If
ActiveWorkbook.save[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]MyNote = "Would you like to move this loan to the Closed Loans tab?"[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If Worksheets("Status of Closing").Range("D5").Value = "Closed" Then
answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Closed Loan")[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If answer = vbYes Then
Lastrow = Sheets("Closed Loans").Range("A65536").End(xlUp).End(xlUp).Row
Lastrow1 = Sheets("Tracker").Range("A65536").End(xlUp).End(xlUp).Row
End If
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]For Each Rng In Sheets("Tracker").Range("A2:A" & Lastrow1)
If Rng = Worksheets("Status of Closing").Range("D5").Value Then
Rng.EntireRow.Copy Sheets("Closed Loans").Cells(Lastrow + 1, 1)
Rng.EntireRow.Delete
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Next[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If answer = vbNo Then
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Exit Sub[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]
Application.EnableEvents = True
End Sub
[/I][/B][/FONT][/COLOR][/LEFT]
[B][I][LEFT][COLOR=#222222][FONT=Verdana]