change number format based on another cell?

Hi Everyone,

I'm new to this and also VBA so please show me some 'nab patience'
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!

I created a workbook for staff planning and it's becoming a behemoth!

I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row.
I have played about with a few things and can't seem to get it to work. What I have so far is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Target, Range("B:B"))
If UCase(myCell.Value) = "P/T" Then
Cells(myCell.Row, 3).NumberFormat = "[hh]:mm"
End If
If UCase(myCell.Value) = "F/T" Then
Cells(myCell.Row, 3).NumberFormat = general
End If
Next myCell
End Sub

Sample data:

A B C D E
Staff member 1 F/T 27 3 24
Staff member 2 F/T 20 3 17
Staff member 3 P/T 176:00 3 4.333333333
Satff member 4 P/T 170:00 3 4.083333333
Staff member 5 F/T 20 3 17

Column C has an 'IF' nested with 'vlookup' to reference from another sheet based on column B value

Column D has the total leave taken so far in the year

Column E is 'C' minus 'D' = leave remaining.

So, I want D and E to have the same number format as C.

I can't do this manually as this sheet auto updates the staff data as new staff join and staff leaving which would mean these manually formatted cells would be out of sync as rows are auto inserted/deleted.

I have tried to be as precise as I can but if I missed anything out, please ask.

Thanks in advance!

Do you mean ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Target, Range("B:B"))
    myCell.Offset(,1).Resize(,3).NumberFormat = IIF(UCase(Target.Value)="P/T","[hh]:mm","General")
Next myCell
End Sub