Add Xlookup to a range via Excel VBA

I tried searching for a solution to my XLookup via VBA problem but I couldn't find one. I have this below data set:

In the Data Set, If any cell in the range C2:C6 is blank, I want to use this formula =IF(ISBLANK(B2),"",XLOOKUP(B2,A:A,IF(ISBLANK(D:D),"",D:D))) in those cells. Where row number of B2 is variable depending upon the row we are putting this formula via VBA.

If any cell in the range C2:C6 has value, I want to use that value without any formula. And if someone deletes the value and the cell becomes blank, VBA will add above formula to that cell.

Currently in the screenshot above, all the cells in range C2:C6 has above formula.

I hope I made sense. If this is not doable, it's okay. I can always use a helper column. But I think VBA would be a more cleaner way for my Dashboard.

Many Thanks in Advance.