If Left(.Value, 1) = "T" Then .Characters(Start:=1, Length:=1).Font.Color = IIf(arr(rw, 12) = "", -16776961, -11489280) ' T 'Use your method intact, only change worksheet function into vba.
If Mid(.Value, 3, 1) = "A" Then .Characters(Start:=3, Length:=1).Font.Color = IIf(arr(rw, 10) = "", -16776961, -11489280) ' A
If Mid(.Value, 5, 1) = "O" Then .Characters(Start:=5, Length:=1).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 7, 2) = "OF" Then .Characters(Start:=7, Length:=2).Font.Color = IIf(arr(rw, 25) = "", -16776961, -11489280) 'OF
If Mid(.Value, 10, 2) = "VA" Then .Characters(Start:=10, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 10, 1) = "N" Then .Characters(Start:=10, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 13, 1) = "L" Then .Characters(Start:=13, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 12, 1) = "L" Then .Characters(Start:=12, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 13, 1) = "N" Then .Characters(Start:=13, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 12, 1) = "N" Then .Characters(Start:=12, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
can you guide me on the part below please:
For Each c In dTmp.keys
r = r + 3
arr1 = Split(dTmp(c), "+")
.Cells(r, 1).Resize(2, 2) = Split(c, ",")
.Cells(r, 1).Resize(2, 2).Interior.Color = arr1(0)
For i = 1 To UBound(arr1) Step 6
It shall also change the font color accordingly as it changes only the interior color and leaving the font color black for which in some lines the font is not visible where its darker. see row 15-16 . 18-19 for example the font for the lines number and name shall have been white.