Macro to auto replace name

[hajiali]

Hello, I'm needing help with getting a macro that will auto move Names from Range(N6:N140) to Replace names in Column C or K that corresponds with the names in Range(P6:P140). The twist is using the values in Range (O6:O140) to determine if the name needs to be replaced and/or if it needs to replace in the value In Column C or K. Only replace the values if cell in Column O starts with "STW" or "TTW" and not "PSTW". Replace the value in C column if the Start and end time match of the O column.

Ex. using the example below. using the table Range N6:P148 going down the list BILL replaces MICHAEL in column K, JOHN replaces TERRY in column K....ETC. If the name appears Once in column C & K then its a simple replace name in N to where the name in P appear in C or K. for the ex. PHILLIP replaces JASON in column C and not in column K because CELL O10 = "STW 0500-1330" and not "STW 1600-2000". and DANNY does not get replaced because value O12 = "PSTW 0500-1000" which is not a full shift. see final product below this table

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4		DAN	5:00	-	13:30		13:00	-	21:30	ROBERT			SHIFT TRADES
5		TOM	5:00	-	13:30		13:00	-	21:30	MICHAEL			NAME	TIME (TTW, PSTW)	FOR
6		ROB	5:00	-	13:30		13:00	-	21:30	DAVID			BILL	STW 1300-2130	MICHAEL
7		MIKE	5:45	-	14:15		16:00	-	20:00	PETER			JOHN	STW 1300-2130	TERRY
8													RACHEL	STW 1330-2200	MILISSA
9		JEN	5:00	-	13:30		13:00	-	21:30	HARRY			CORY	STW 1600-2000	KOBY
10		PAGE	5:00	-	13:30		13:00	-	21:30	TERRY			PHILLIP	STW 0500-1330	JASON
11		TED	5:00	-	13:30		13:00	-	21:30	CLIFFORD			DEREK	STW 0500-1330	JEN
12		ASHLEY	5:45	-	14:15		16:00	-	20:00	JASON			BOB	PSTW 0500-1000	DANNY
13
14		DANNY	5:00	-	13:30		13:00	-	21:30	BRIAN
15		SARAH	5:00	-	13:30		13:00	-	21:30	THOMAS
16
17		LISA	5:30	-	14:00		14:00	-	22:30	MARY
18
19		JASON	5:00	-	13:30		13:00	-	21:30	ALFORD
20		ERIC	5:00	-	13:30		13:30	-	22:00	MILISSA
21		JORDAN	5:00	-	13:30		13:00	-	21:30	CRYSTAL
22		VICTORA	5:45	-	14:15		14:00	-	22:30	MONDAY
23		SMITH	5:45	-	14:15		14:00	-	22:30	APRIL
24		KYLE	5:45	-	14:15		16:00	-	20:00	KOBY
BASE SCH

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C24,K4:K24,P6:P21	Cell Value	duplicates	text	NO

Result below:

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4		DAN	5:00	-	13:30		13:00	-	21:30	ROBERT			SHIFT TRADES
5		TOM	5:00	-	13:30		13:00	-	21:30	BILL			NAME	TIME (TTW, PSTW)	FOR
6		ROB	5:00	-	13:30		13:00	-	21:30	DAVID			BILL	STW 1300-2130	MICHAEL
7		MIKE	5:45	-	14:15		16:00	-	20:00	PETER			JOHN	STW 1300-2130	TERRY
8													RACHEL	STW 1330-2200	MILISSA
9		DEREK	5:00	-	13:30		13:00	-	21:30	HARRY			CORY	STW 1600-2000	KOBY
10		PAGE	5:00	-	13:30		13:00	-	21:30	JOHN			PHILLIP	STW 0500-1330	JASON
11		TED	5:00	-	13:30		13:00	-	21:30	CLIFFORD			DEREK	STW 0500-1330	JEN
12		ASHLEY	5:45	-	14:15		16:00	-	20:00	JASON			BOB	PSTW 0500-1000	DANNY
13
14		DANNY	5:00	-	13:30		13:00	-	21:30	BRIAN
15		SARAH	5:00	-	13:30		13:00	-	21:30	THOMAS
16
17		LISA	5:30	-	14:00		14:00	-	22:30	MARY
18
19		PHILLIP	5:00	-	13:30		13:00	-	21:30	ALFORD
20		ERIC	5:00	-	13:30		13:30	-	22:00	RACHEL
21		JORDAN	5:00	-	13:30		13:00	-	21:30	CRYSTAL
22		VICTORA	5:45	-	14:15		14:00	-	22:30	MONDAY
23		SMITH	5:45	-	14:15		14:00	-	22:30	APRIL
24		KYLE	5:45	-	14:15		16:00	-	20:00	CORY
BASE SCH

The yellow Highlights of the Manual Changes I need the Macro to accomplish. any help is greatly appreciated.

 

[ManiacB]

Give this code a try. It also highlights the updated cells.

Sub comrepl()
Dim i, j, lrn, lrb As Long

lrn = Cells(Rows.Count, "O").End(xlUp).Row
lrb = Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To lrb
    For j = 1 To lrn
    If Cells(i, "B").Value = Cells(j, "O").Value And Left(Cells(j, "N").Value, 1) = "S" Then
        Cells(i, "B").Value = Cells(j, "M").Value
        Cells(i, "B").Interior.ColorIndex = 6 ' Remove line to stop updating cells
    End If
    If Cells(i, "J").Value = Cells(j, "O").Value And Left(Cells(j, "N").Value, 1) = "S" Then
        Cells(i, "J").Value = Cells(j, "M").Value
        Cells(i, "J").Interior.ColorIndex = 6 ' Remove line to stop updating cells
    End If
    Next j
Next i
End Sub

 

[hajiali]

Thank you ManiacB this was awesome. I did have to change most Column reference to match what I have but overall it was great.