Cannot use Custom Number Format. Need help with a VBA code

I created a pivot table and using data from the sites tab (see attached file). In order to transfer the business hours access info over to the pivot table (sheet 1), I created a Legend column (columns B & C) 1=Full, 2=Ltd, 3=full. Under the values, I customized the number format to [=1]"Full";[=2]"Ltd";"None". Here’s the problem I have to add 4=NS; 5=Closed. Unfortunately custom number format only allows 3 changes. The only alternative I see is to use a vba code. Can anyone help me?

I’m a novice to vba. I tried this for Sheet 1 but it got me nowhere.

Sub Substitute()
Dim rng As Range, Cell As Range
Set rng = Sheets("Sheet1").Range("B1:B:629)
For Each Cell In rng
Cell = WorksheetFunction.replace(Cell, "1", "Full")
Cell = WorksheetFunction.replace(Cell, "2", "Ltd")
Cell = WorksheetFunction.replace(Cell, "3", "None")
Cell = WorksheetFunction.replace(Cell, "4", "NS")
Cell = WorksheetFunction.replace(Cell, "5", "Closed")
Next
End Sub

I also need to do the same for column C.