VBA color Character font in a Cell Line Feed Problem

[Stephen_IV]

I have some code below and it works fine for most of the data sets that I have. The problem is that I have data that is put into a cell using Ctrl+ Enter or Char(10). i need to update the code to handle Chr(10). Can Someone please assist me.

Thanks in Advance.

The table below does not show the Char(10) but this is what the data looks like.

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Course</th></tr></thead><tbody>
<tr><td>STEM LAB III [1] (13)</td></tr>
<tr><td>"US HISTORY [4] (2)</td></tr>
<tr><td>PERSPECTIVES ON RACE [4] (3)</td></tr>
<tr><td>WORLD CIVILIZATION [10] (4)"</td></tr>
<tr><td>HEALTH [16] (24)</td></tr>
<tr><td>"PHYSICAL SCIENCE [2] (5)</td></tr>
<tr><td>ENVIRONMENTAL SCIENCE [9] (3)"</td></tr>
</tbody></table>

Sub InsideBrackets()
Dim x As Long
Dim y As Long
Dim xcell As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each xcell In Range("B2:H" & lastrow)
        x = InStr(xcell.Value, "(")
        y = InStr(xcell.Value, ")")
        xcell.Characters(x + 1, y - 1 - x).Font.ColorIndex = 3
    Next xcell
End Sub

 

[Rick Rothstein]

Your need is not clear to me. Is your code supposed to be coloring the font inside the parentheses red? If so, what is happening that shouldn't happen (your posted example is not showing any Line Feeds, so the problem they are causing you is not apparent).

 

[Stephen_IV]

Sorry Rick,

I need to color all of the data inside of the parenthesis, my code does not color all of the line feeds. Just the first. I need them all colored inside the Parenthesis.


<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Course</th></tr></thead><tbody>
<tr><td>STEM LAB III [1] (13)<br>
STEM LAB II [7] (11)</td></tr>
<tr><td>US HISTORY [4] (2)</td></tr>
<tr><td>PERSPECTIVES ON RACE [4] (3) <br>
WORLD CIVILIZATION [10] (4)<br>
HEALTH [16] (24)<br></td></tr>
<tr><td>PHYSICAL SCIENCE [2] (5)<br>
ENVIRONMENTAL SCIENCE [9] (3)</td></tr>
</tbody></table>

 

[mse330]

Does the below code do what you need ?

Sub ColorRed()
Dim cell As Range, lRow As Long, StartPos As Long, EndPos As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In Range("B2:H" & lRow)
        StartPos = 1
        For i = 1 To Len(cell.Value) - Len(Replace(cell.Value, "(", ""))
            StartPos = InStr(StartPos + 1, cell.Value, "(")
            EndPos = InStr(StartPos + 1, cell.Value, ")")
            cell.Characters(StartPos + 1, EndPos - StartPos - 1).Font.ColorIndex = 3
        Next i
    Next
End Sub

 

[Stephen_IV]

mse330 Perfect!!!!!! Thank you very much! That is exactly what I needed!

 

[mse330]

Glad to help & thanks for the feedback