title: Using VBA to change the colour of text based on starting character of sentence
body: <p>I am trying to use a code to conditionally format multiple lines of text in one cell so that if it starts with a <code>#</code> it remains black, if it starts with a <code>~</code> it changes to red and if it doesn't start with a <code>#</code> or <code>~</code> it becomes <strong>bold</strong>. </p>
  
  <p>I can get the first two bits to work but not the last bit, I don't know what to put instead of the <code>?</code> I have below so that if the line starts with anything other than <code>#</code> or <code>~</code> it becomes bold. </p>
  
  <pre><code>Sub Conditional_Format()
  
      Dim sString, eStringA, eStringN, eString, x As Double
      Dim count1, count2, sum As Double
      Dim iString As String
  
      ' Selects the active cell
      Sheets("Sheet1").Select
      Range("C4").Select
  
      ' Counts the number of entries in the cell
      count1 = Len(ActiveCell.Value) - Len(Replace(ActiveCell.Value, "#", ""))
      count2 = Len(ActiveCell.Value) - Len(Replace(ActiveCell.Value, "~", ""))
      sum = count1 + count2
  
      ' Sets sString at the start position of the cell
      sString = 1
  
      ' Main Loop
      For x = 1 To sum
  
          ' Determins the location of where each symbol next appears
          eStringA = InStr(sString + 1, ActiveCell.Value, "#")
          eStringN = InStr(sString + 1, ActiveCell.Value, "~")
  
          ' Sets the location of the closest symbol
          If eStringN = 0 Then
              eString = eStringA
          ElseIf eStringA = 0 Then
              eString = eStringN
          ElseIf eStringA &gt; eStringN Then
              eString = eStringN
          Else: eString = eStringA
          End If
  
          ' Sets the string of text being examined as iString
          If eString = 0 Then
              iString = Mid(ActiveCell.Value, sString)
          Else: iString = Mid(ActiveCell.Value, sString, eString - sString - 1)
          End If
  
          ' Colours the string based on if a # can be found in the string
          If InStr(iString, "#") &lt;&gt; 0 Then
              ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
              ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
          ElseIf InStr(iString, "~") &lt;&gt; 0 Then
              ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(225, 0, 0)
              ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
  
  
          ElseIf InStr(iString, ?))  Then
              ActiveCell.Characters(sString, eString - sString - 1).Font.Bold = True
              ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
              ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
          End If
  
          ' Moves the start point up for the next item
          sString = eString
  
      Next x
  
  End Sub
  </code></pre>