Thanks for the support,please help I have attached the code that I tried doing the below
I have a issue where I have to retain the font color of each words in cell that has delimiter comma.
first i do validation of checking the cell value if the first letter is capital and the Initials are all capital then join them back to display in cell but currently the font color are changing to last word color
eg: Arun a, kmaal k,Jack js
if Arun a is yellow kmaal k is red Jack js is saffron
then it should display Arun A, Kmaal K, Jack JS in the same color each word mentioned above however below code is showing all in same color
Dim cellvalued As String
Dim wordarrayed As Variant
Dim newtext
Dim wordarrayed2 As Variant
Dim Cell As Range
Dim Val
Dim p As Long
Dim orgnal As Long
Dim modcelled As String
cellvalued = Range("h" & t).Value
Set Cell = ThisWorkbook.Sheets("Congress Materials").Range("h" & t)
Val = Cell.Value
wordarrayed = Split(cellvalued, ",")
For p = LBound(wordarrayed) To UBound(wordarrayed)
orgnal = Cell.Characters(Start:=InStr(Cell.Value, wordarrayed(p)), Length:=Len(wordarrayed(p))).Font.Color
wordarrayed(p) = StrConv(wordarrayed(p), vbProperCase)
wordarrayed2 = Split(wordarrayed(p), " ")
If UBound(wordarrayed2) > 0 Then
wordarr = StrConv(wordarrayed2(o + 1), vbUpperCase)
wordarrayed(p) = wordarrayed2(o) & " " & wordarr
End If
Cell.Characters(Start:=InStr(Cell.Value, wordarrayed(p)), Length:=Len(wordarrayed(p))).Font.Color = orgnal
Next p
modcelled = Application.WorksheetFunction.TextJoin(",", True, wordarrayed)
'modcelled = orgnal
Range("h" & t).Value = modcelled
Bookmarks