Results 1 to 2 of 2

vba validaton font color issue

Threaded View

kamalvenug vba validaton font color issue 12-07-2024, 07:08 AM
AliGW Re: vba validaton font color... 12-07-2024, 07:14 AM
  1. #1
    Registered User
    Join Date
    12-07-2024
    Location
    Chennai,Tamilnadu
    MS-Off Ver
    365
    Posts
    1

    Question vba validaton font color issue

    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
    Attached Images Attached Images
    Last edited by 6StringJazzer; 12-07-2024 at 11:35 AM. Reason: cleaned up redundant code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-31-2021, 03:23 PM
  2. [SOLVED] Issue with sorting data on conditional formatting/font color
    By n_ant in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2014, 04:30 PM
  3. Replies: 2
    Last Post: 02-04-2013, 02:00 PM
  4. Change font color based on font color of another cell
    By Ibyers in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2012, 09:36 AM
  5. Infuriating VB Excel chart issue - how to change y axis font color
    By Ace of Clubs in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-07-2009, 01:22 AM
  6. Changing Cell Font and Color Issue,
    By RitaG in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-31-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1