Using VBA to change the colour of text based on starting character of sentence

Question

I am trying to use a code to conditionally format multiple lines of text in one cell so that if it starts with a # it remains black, if it starts with a ~ it changes to red and if it doesn't start with a # or ~ it becomes bold.

I can get the first two bits to work but not the last bit, I don't know what to put instead of the ? I have below so that if the line starts with anything other than # or ~ it becomes bold.

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 > 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, "#") <> 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, "~") <> 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

Can you give an example of how the text on the sheet you're working with might look? I can't tell if it's a lot of text within one cell or many cells — Marcucciboy2, Oct 01 '18 at 12:53
It's a lot of text in one cell , e.g the following three lines in one cell: #...... ~...... 2.... — Jono Adam, Oct 01 '18 at 13:18
I know you've got a workable solution, but I've got to ask. It seems that you have "lines" of text in an Excel cell that are `vbCrLf` delimited, but you're counting only the number of `#` and `~` in the cell to determine the number of "lines". If you're not looking for `vbCrLf` in the cell, how do you know there is a "line" that _doesn't_ begin with either `#` or `~`? — FreeMan, Oct 01 '18 at 14:34

score 2 · Accepted Answer · answered Oct 01 '18 at 14:10

As I understand your question, you're successfully dividing up your "lines" within a cell, setting your # and ~ text as necessary and trying to figure out how to change the other "lines" within the cell.

To do that simply change your ElseIf to Else:

  If InStr(iString, "#") <> 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, "~") <> 0 Then
    ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(225, 0, 0)
    ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
  'vvvv change this line
  '--- Lines starting with any character OTHER than "#" or "~" will fall in here
  Else
    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

Glad you found it helpful! I'd suggest you read up on [how to avoid using .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Your use of `.Select` and `Active*` is a minefield waiting to blow up in your face. It's not difficult to do and it will give you _much_ more robust code. Also, feel free to click the "check mark" below the voting icons if this is the answer you found most helpful. — FreeMan, Oct 01 '18 at 14:32

score 0 · Answer 2 · edited Oct 01 '18 at 13:55

0

I used this and it seems to work (as long as the # or ~ is the first character of the cell.

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

For i = 1 To ws.UsedRange.Rows.Count
    For x = 1 To ws.UsedRange.Columns.Count

        If Left(Cells(i, x), 1) = "#" Then
            ' Cells(i, x).Interior.ColorIndex = 7
        ElseIf Left(Cells(i, x), 1) = "~" Then
            Cells(i, x).Interior.ColorIndex = 3
        ElseIf Left(Cells(i, x), 1) <> "#" Or Left(Cells(i, 1), 1) <> "~" Then
            Cells(i, x).Font.Bold = True
        End If

    Next x
Next i

edited Oct 01 '18 at 13:55

Marcucciboy2

3,156
3
20
38

answered Oct 01 '18 at 13:27

TAS1987

71
1
12

1

Unfortunately, OP has many sentences within a single cell, which changes the answer a bit – Marcucciboy2 Oct 01 '18 at 13:54
I think this only works if text is in separate cells which it isn't – Jono Adam Oct 01 '18 at 13:54

Using VBA to change the colour of text based on starting character of sentence

2 Answers2