VBA code
Sub loopchange()
'select the blank column
Dim level As String
Range("AF2").Select
Do
'move to A2 cell'
ActiveCell.Offset(0, -31).Select
'find out which is level2
If (ActiveCell.Value = "..2") Then
'move to column AD
ActiveCell.Offset(0, 28).Select
'Store the first 4 digit for level 2
level = Left(ActiveCell.Value, 4)
'Move to the column A3
ActiveCell.Offset(1, -28).Select
End If
MsgBox Left(ActiveCell.Value, 4)
'compare whether it's level 3(VBA don't detect this)
If (ActiveCell.Value = "...3") Then
'move to the column AD
ActiveCell.Offset(0, 28).Select
'compare the stored first 4 digit in level 2 known as level to first
'4 digit of current cell
If (level = Left(ActiveCell.Value, 4)) Then
'move to column AF
ActiveCell.Offset(0, 2).Select
'input the word NO CTH
ActiveCell.Value = "No CTH"
End If
End If
Loop Until IsEmpty(ActiveCell.Offset(0, -31))
End Sub
My rationale is to compare level 2(..2) first four digit to level 3(...3) first four digit and input the world "No CTH" into column AF3 using a Do loop.
(PS my code stuck at " If (ActiveCell.Value = "...3") Then" the vba code doesn't run this and went straight to the End if statement)