Vba validation issue Help

[kamalvenug]

Hi everybody,

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

 

[kamalvenug]

Hi everybody,

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

                    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 = wordarrayed(p).Font.ColorIndex
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
'wordarrayed(p).Characters
End If
'newtext = wordarrayed(p)
Cell.Characters(Start:=InStr(Cell.Value, wordarrayed(p)), Length:=Len(wordarrayed(p))).Font.Color = orgnal
Next p
modcelled = Application.WorksheetFunction.TextJoin(",", True, wordarrayed)

Range("h" & t).Value = modcelled

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