Format cells to automatically change MAC Address to 00:00:00:00:00:00 format

[Briant2468]

Hello,

I am trying to set up a column to automatically change what ever is typed or pasted to be in a MAC Address format with the colons i.e. if typed or pasted 112233445566 it will show up as 11:22:33:44:55:66. I also want to copy the address and it be 11:22:33:44:55:66.
I have tried using =CONCATENATE(MID(A1,1,2),":",MID(A1,3,2),":",MID(A1,5,2),":",MID(A1,7,2),":",MID(A1,9,2),":",MID(A1,11,2)) and that does not work well.

I also have tried a VBA for the first time and it works good but if I apply it to an existing column I have to highlight each MAC and hit enter for the colons to show up. If I paste a column of MACs it formats the but it increases the MAC by 1 for each row like a pyramid (See picture below).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
Dim n As Long
Dim strTemp As String
Const csMONITOR_RANGE As String = "A1:A100"

On Error GoTo clean_up

If Not Intersect(Target, Range(csMONITOR_RANGE)) Is Nothing Then
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Range(csMONITOR_RANGE)).Cells
If Len(rngCell.Value) = 12 Then
For n = 1 To 12 Step 2
strTemp = strTemp & ":" & Mid$(rngCell.Value, n, 2)
Next n
rngCell.Value = Mid$(strTemp, 2)
End If
Next rngCell
End If

clean_up:
Application.EnableEvents = True

End Sub

I assumed this would be something easy to do with MAC Address being entered a lot, but it is kicking my butt.

Thank You
Brian

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Extension	Inactive Days	Date Tested	Column1	Old MAC	Description	New Mac	Notes
2	88901	0	6/23/2021	3	2F:E2:21:3B:88:21
3	88902	10	4/15/2021	82	2F:E2:21:3B:88:22
4	88903	14	6/23/2021	17	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23
5	88904	138	11/17/2020	359	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24
6	88905	32	4/15/2021	104	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25
7	88906	17	4/15/2021	89	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26
8	88907	0	6/23/2021	3	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27
9	88908	231	4/15/2021	303	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28
10	88909	90	6/23/2021	93	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29
11	88910	89	11/17/2020	310	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30
12	88911	0	6/23/2021	3	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31
13	88912	90	4/15/2021	162	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32
14	88913	5	6/23/2021	8	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32:2F:E2:21:3B:88:33
15	88914	91	11/17/2020	312	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32:2F:E2:21:3B:88:33:2F:E2:21:3B:88:34
16	88915	64	6/23/2021	67	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32:2F:E2:21:3B:88:33:2F:E2:21:3B:88:34:2F:E2:21:3B:88:35
17	88916	87	11/17/2020	308	2F:E2:21:3B:88:36
18	88917	74	11/17/2020	295	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32:2F:E2:21:3B:88:33:2F:E2:21:3B:88:34:2F:E2:21:3B:88:35:2F:E2:21:3B:88:37
19	88918	147	11/17/2020	368	2F:E2:21:3B:88:22:2F:E2:21:3B:88:23:2F:E2:21:3B:88:24:2F:E2:21:3B:88:25:2F:E2:21:3B:88:26:2F:E2:21:3B:88:27:2F:E2:21:3B:88:28:2F:E2:21:3B:88:29:2F:E2:21:3B:88:30:2F:E2:21:3B:88:31:2F:E2:21:3B:88:32:2F:E2:21:3B:88:33:2F:E2:21:3B:88:34:2F:E2:21:3B:88:35:2F:E2:21:3B:88:37:2F:E2:21:3B:88:38
20	88919	94	6/23/2021	97	2F:E2:21:3B:88:39
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=TODAY()-C2+B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B20	Cell Value	>90	text	NO

 

[maabadi]

Welcome to MrExcel Message Board.
1. Formula Method:

=TEXTJOIN(":",,MID(A1,1,2),MID(A1,3,2),MID(A1,5,2),MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))

2. VBA Method:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
Dim n As Long
Dim strTemp As String
Const csMONITOR_RANGE As String = "A1:A100"

On Error GoTo clean_up

If Not Intersect(Target, Range(csMONITOR_RANGE)) Is Nothing Then
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Range(csMONITOR_RANGE)).Cells
strTemp = ""
If Len(rngCell.Value) = 12 Then
For n = 1 To 12 Step 2
strTemp = strTemp & ":" & Mid$(rngCell.Value, n, 2)
Next n
rngCell.Value = Mid$(strTemp, 2)
End If
Next rngCell
End If

clean_up:
Application.EnableEvents = True

End Sub