When inserting multiple TMs in TEXT mode, after the 2nd one, all of the text in the cell is changed to the TM color RRS feed

  • Question

  • Hi,

    I am using Excel 2007 with 2003 VBA code. I am facing onle issue is an environmental issue as I am not getting the issue, but my onsite people are getting issue.

    The issue is:

    They have entered some text in a cell, when they are inserting multiple TickMarks from the ribbon, the entered text will be changed to TickMark color.

    As the VBA code not having any code to change the text color.

    I hope this issue is because of any settings in the excel or any environment issue.

    It will be appriciate if you provide the solution for the above issue.




    Friday, August 3, 2012 7:08 AM

All replies

  • Could you be more specific, or attach some picture about that TickMarks.

    As far I understand this code can help you:

    'standard index
    ActiveCell.Font.ColorIndex = 4
    'naumber or named
    ActiveCell.Font.Color = vbRed
    With ActiveCell
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""a""" 'any formula
        .FormatConditions(1).Font.Color = -16383844 'vbred
        .FormatConditions(1).StopIfTrue = False
    End With
    more what you can do with colorindex

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, August 3, 2012 12:14 PM
  •   Thanks for ur reply,

    I have Issue like as shown in below image entered text "Divakar" in Black color and inserted TM in red color. Now I inserted TM 2 then entire text changed to red color. I want the "Divakar" text to be in same color Black. and the TMs should be in Red only. pls look into my code below:

    'If in text mode
                If TextPress Then
                String1 = Target.Value 'Create temporary string with text currently being held
                Target.FormulaR1C1 = TM_Chr & String1 'Insert TM_Chr at beginning of cell, with existing text remaining
                   With Target.Characters(Start:=1, Length:=Len(TM_Chr)).Font 'For characters specific to TM_Chr inserted
                        'Set specific text attributes
                        .Color = ColorLevel
                        .Size = FontSize
                        .Bold = False
                        .Italic = False
                        .Strikethrough = False
                        .Superscript = False
                        .Subscript = False
                        .OutlineFont = False
                        .Shadow = False
                        .Underline = xlUnderlineStyleNone
                        .ThemeFont = xlThemeFontNone
                        If Not IsNull(FontName) Then .Name = FontName
                    End With
                Else 'If in stamp mode


    Tuesday, August 7, 2012 2:45 AM
  • and in the code the TICKMarks showing as "?", pls help me to find or count of tickmarks in the active cell.


    Tuesday, August 7, 2012 2:52 AM
  • Ok, take look on this gallery

    There I used Bold signs in string, but no matter, couse this code count string (Start and Length Characters) to make modification.

    You can change it for color with easy too.

    Did you handle this or more help is needed?

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, August 7, 2012 10:37 AM
  • THe txt file code having bit confusion, Can you pls help me for the solution for me?


    Tuesday, August 7, 2012 11:21 AM
  • ok - you have in red font cell, filed some words and in the middle word "Divakar" and something else.

    Using code I showing you before, i use some of this line and I rewrite something like this:

    Option Explicit

    Public Const zamiana As String = "Divakar"

    Sub Change_Color_from_cell(wartosc As Range, szukaj$) On Error GoTo koniec If InStr(1, wartosc.Value, szukaj) > 0 Then Dim y&, dl&, gdzie As Variant gdzie = Split(Replace(wartosc.Value, ",", " ")) For y = LBound(gdzie) To UBound(gdzie) - 1 dl = dl + Len(gdzie(y)) + 1 If gdzie(y + 1) = szukaj Then _ wartosc.Characters(Start:=dl + 1, Length:=Len(szukaj)).Font.Color = vbBlack 'vbred or ather Next y End If koniec: End Sub

    and when I have some cells with word "Divakar" I can use this code to change color to Black (or whatever I want replaced in code)

    Sub formatuj_kolor()
    Dim rn As Range
    For Each rn In Selection
     'rn.Font.ColorIndex = xlAutomatic 'if you want delete format
     Call Change_Color_from_cell(rn, zamiana)
    End Sub

    alse, I can use automatic replacement color, adding this code (below) to SheetsModule (not general module I added above codes)

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Call Change_Color_from_cell(Target, zamiana)
    End Sub

    Try it ;]


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, August 7, 2012 1:36 PM