title: How do I force the font color to remain the way it is rather than changing
body: <p>My program basically highlights the keywords in text to the user choice font color.I have a problem only when displaying the search terms in the cell that is specified by the user not in actual execution of the program. I take the terms input, range/cell to display these terms, font color from the user. This is partial code:</p>
  
  <pre><code> Dim Ran As String
            searchTerms = InputBox("Please enter words to search, if more than one string seperate by comma and space", "Need Input", 1)
  
  
         Ran = InputBox("Please enter cell where you want the search terms to be displayed ideally below verbatim like C2,D2 ", "Need Input", 0, 8)
        r = Range(Ran).Row
        c = Range(Ran).Column
  
         If IsEmpty(Cells(r, 1)) And c &lt;&gt; "A" Then
           Range(Ran).Value = Range(Ran).Value &amp; ", " &amp; searchTerms
          Else: Range(Ran).EntireRow.Insert
          Range(Ran).Value = searchTerms
          End If
  
         searchTerms = Split(UCase(searchTerms), ", ")
  </code></pre>
  
  <p>This works as expected for 2 executions but for third time execution the previous execution font color changes to the 1st execution color. How do I force it to remain as it was before execution. Suppose after 1st time execution it was yellow, After Second execution the appended variables were green, Then when I am performing the third execution the whole cell content change to yellow before peforming the execution and the only the third execution terms will change to user defined font color. But the Second execution appended terms will be yellow as first execution color.
  This is the code which is highlighting function:</p>
  
  <pre><code>Function HilightString(offSet As Integer, searchString As String, rowNum As Long, ingredCol As String, FontColor, fontSize As Integer) As Integer
  
          Dim x As Integer
          Dim newOffset As Integer
          Dim targetString As Variant
  
           If Cells(rowNum, ingredCol).HasFormula Then
  Cells(rowNum, ingredCol).Value = "'" &amp; Cells(rowNum, ingredCol).Formula
  End If
          targetString = Mid(Cells(rowNum, ingredCol), offSet)
          foundPos = InStr(UCase(targetString), searchString)
  
          If foundPos &gt; 0 Then
  
              Cells(rowNum, ingredCol).Characters(offSet + foundPos - 1, Len(searchString)).Font.ColorIndex = FontColor
  
              Cells(rowNum, ingredCol).Characters(offSet + foundPos - 1, Len(searchString)).Font.Size = 14
  
              newOffset = offSet + foundPos + Len(searchString)
  
              x = HilightString(newOffset, searchString, rowNum, ingredCol, FontColor, fontSize)
          Else
  
              Exit Function
  
          End If
   End Function
  </code></pre>
  
  <p>First execution highlighted turn, signal in blue and created in cell c2:<a href="https://i.stack.imgur.com/khCvs.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/khCvs.jpg" alt="After 1st execution"></a></p>
  
  <p>Second execution highlighted Rear in green It works fine till this point<a href="https://i.stack.imgur.com/8LdLa.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8LdLa.jpg" alt="Second execution"></a></p>
  
  <p>Before complete third execution highlighted blink <a href="https://i.stack.imgur.com/Z6W66.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Z6W66.jpg" alt="Before complete 3rd execution"></a></p>
  
  <p>After 3rd execution. The second executed words turn back to blue <a href="https://i.stack.imgur.com/OD12n.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OD12n.jpg" alt="enter image description here"></a></p>