Changing certain text within a cell

  • Thread starter Thread starter Brad1982
  • Start date Start date
B

Brad1982

I make a production schedule on a daily basis and it has anywhere from
60-120 SKU's per day. I am running into the problem of having too many
SKU's that are similar to eachother within one cell. This makes it hard
for me to double check my schedule without looking very closely at each
letter.

A1 would contain this
TBLUE(50)
THMBLU(60)
THBLU(10)
THMBLU(47)

Is there a formula I could use to color the prefix "THM" blue? I could
then make the prefix "TH" a different color etc etc.. I want to avoid
doing this manually as it would be quite time consuming for 120 SKU's.
 
This is very easy if you use VBA:

Sub only_blue()
Dim L As Long
For L = 1 To 100
If Left(Cells(L, 1).Value, 3) = "THM" Then
Cells(L, 1).Characters(Start:=1, Length:=3).Font.ColorIndex = 5
End If
Next L
End Sub

This little program runs down the first 100 cells in column A and, if they
start with THM, will make the THM blue.

You can adjust the limits to suit your needs.
 
You are correct Biff:

If ther are multiple strings within A1 containing THM, then the code would
update only the leading instance. In that case:

1. copy the material to a Word doc.
2. use find/replace in Word to change THM into THM (blue)
3. copy the material back into Excel
 
Back
Top