VBA Code to change one cell test based on another cell

[bradw499]

I need a VBA script the will allow me to look down a column and find a specific name then "Offset" to the left of that cell and change the value of the "Offset" cell. I have tried several scripts but no luck. I have just started to learn VBA and it is killing me. I will not give up or quit. Any help, suggestions, comments etc.... will be greatly appreciated.
Below is the script I have been working with and the test data that I am using. I need the script to look for "John Smith" under "Employee_Name then move to the left 1 cell and change the cell value to "OFFICER". I have about 35,000 rows of data to loop through to change 12 people from "Clerical" to "Officer" This is an every month process.

Thanks,
Bradw499

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Range("I:I").Select
For Each Cell In Sheets(1).Range("I:I")
If Cell.Value = ("JOHN SMITH") Then
ActiveCell.Offset(0, -1).Value = "OFFICER"
End If
Next[/FONT]

[TABLE="width: 905"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 992"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Paydate[/TD]
[TD]Adjust[/TD]
[TD]Employee_Code[/TD]
[TD]Workers_Code[/TD]
[TD]Classification[/TD]
[TD]Employee_Name[/TD]
[TD]Department[/TD]
[TD]Department[/TD]
[TD]Position[/TD]
[TD]Pay_Type[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1110[/TD]
[TD]100132[/TD]
[TD]OFFICER[/TD]
[TD]JOHN SMITH[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]OFFICER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1111[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]ADAM SMITH[/TD]
[TD]1900[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]JAMES BROWN[/TD]
[TD]2950[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1113[/TD]
[TD]100132[/TD]
[TD]OFFICER[/TD]
[TD]CHARLIE BROWN[/TD]
[TD]1600[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1114[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]TOM LEGG[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1115[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]MARY LEGGINS[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1116[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]JOE FRYER[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1117[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]TOM FRYER[/TD]
[TD]2920[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1118[/TD]
[TD]100135[/TD]
[TD]OFFICER[/TD]
[TD]MANNY MOUTH[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1119[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]JOHN GREEN[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1120[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]TOM GREEN[/TD]
[TD]2960[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1121[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]ANDY TIME[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1122[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]CINDY HAPPY[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1123[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]PAUL BUNION[/TD]
[TD]2100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1124[/TD]
[TD]100136[/TD]
[TD]OFFICER[/TD]
[TD]CHARLIE DANIELS[/TD]
[TD]2950[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1125[/TD]
[TD]100136[/TD]
[TD]OFFICER[/TD]
[TD]JOHN SMITH[/TD]
[TD]1900[/TD]
[TD]OFFICE[/TD]
[TD]OFFICER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1126[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]ADAM SMITH[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1127[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]JAMES BROWN[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1128[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]CHARLIE BROWN[/TD]
[TD]2100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1129[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]TOM LEGG[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1130[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]MARY LEGGINS[/TD]
[TD]2500[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1131[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]JOE FRYER[/TD]
[TD]1900[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1132[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]TOM FRYER[/TD]
[TD]1200[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1133[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]MANNY MOUTH[/TD]
[TD]1000[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1134[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]JOHN GREEN[/TD]
[TD]1400[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1135[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]TOM GREEN[/TD]
[TD]1900[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1136[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]ANDY TIME[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1137[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]CINDY HAPPY[/TD]
[TD]1200[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1138[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]PAUL BUNION[/TD]
[TD]2100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1139[/TD]
[TD]100136[/TD]
[TD]CLERICAL[/TD]
[TD]CHARLIE DANIELS[/TD]
[TD]1700[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1110[/TD]
[TD]100132[/TD]
[TD]OFFICER[/TD]
[TD]JOHN SMITH[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]OFFICER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1111[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]ADAM SMITH[/TD]
[TD]2400[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]JAMES BROWN[/TD]
[TD]2940[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1113[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]CHARLIE BROWN[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1114[/TD]
[TD]100132[/TD]
[TD]CLERICAL[/TD]
[TD]TOM LEGG[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1115[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]MARY LEGGINS[/TD]
[TD]2100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1116[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]JOE FRYER[/TD]
[TD]1100[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2018[/TD]
[TD][/TD]
[TD="align: right"]1117[/TD]
[TD]100135[/TD]
[TD]CLERICAL[/TD]
[TD]TOM FRYER[/TD]
[TD]2910[/TD]
[TD]OFFICE[/TD]
[TD]WORKER[/TD]
[TD]Hourly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Ok try this...instead of having to change for each person and rank put their name and rank in cells somewhere on the sheet....I've used A1 and B1
That way the code never changes and you just change the cells on the sheet, and run the code

Sub MM1()
Dim cell As Range, lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
For Each cell In Range("I2:I" & lr)
    If cell.Value = Range("A1").Value Then
        cell.Offset(0, -1).Value = Range("B1").Value
    End If
Next
End Sub

 

[bradw499]

Thank you so much for the quick response.
The only issue is I have other data on that sheet that pulls from those cells.
There are over a dozen sheets that pull data from this sheet. I have a macro that gets the raw data and puts it into that specific sheet.
I did create a special sheet that I could pull from. it is simply called "Names"
how do I upload a sample of my entire sheet like you requested?
Again, THANK YOU very much.
Brad

 

[Michael M]

how do I upload a sample of my entire sheet like you requested?

I didn't request an upload ?

You will need to provide more information on the new issues you are having.
If you do want to upload your workbook, either upload to dropbox and post a link back here
OR
have a look here for other options of posting attachments

https://www.mrexcel.com/forum/about-board/508133-attachments.html