Excel Macro: Allows User to Change Part of Cell Content (not all of the content)

For Background, I have a cell that reads: "The Prescription Model for Exterior Applications etc...." when I go to alter the character in the cell (to delete or add content) I can change the formatting of the text. I.e. I can change the font, size, color etc., without altering the other characters within that same cell.

So, my question is, can we create a macro that will allow the user to change/alter a selection of text from within a cell BUT WHICH DOES NOT CHANGE/ALTER other information in that cell?

Here's an example of what I'm talking about

"The Prescription Model for Exterior Applications etc..." (I want to change/alter as follows:)

"The Prescription Model for Exterior Applications etc..."

is this possible? I've tried for days, but I cannot figure this one out. I've probably thought myself into a corner on this one. Please help.

Thank you in advance! :)

Quote:

---

is this possible?

---

yes loop through all characters in cell and change their properties

here is a sample

Code:

---

t = "Exterior Applications"
With Range("b11")
    strt = InStr(.Value, t)
    For cr = strt To strt + Len(t)
        .Characters(cr, 1).Font.Size = 14
    Next
End With

---

where t is the text to change and B11 contains the value to change

the problem is that I do not want the range to be static. I need to be able to select whatever text I decide to alter within that text, change it, and then move on to the next cell and decide which text I would like to select and alter it as well.

Am I not seeing something here?

westconn1,

here is an example of my code. Much appreciated if you can help.

'
' Redden Macro
' reddens selected text with cell
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"The percentage of the planned work that was accomplished is:"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 11
.Color = -13421773
End With
With ActiveCell.Characters(Start:=23, Length:=12).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 14
.Color = -16776961
End With
With ActiveCell.Characters(Start:=35, Length:=26).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 11
.Color = -13421773
End With
End Sub

Quote:

---

Am I not seeing something here?

---

how would you input dynamic text to the procedure and also which cell? you could use an inputbox but by that point you could almost do it as easily manually

edit: your last post which i just see now, is no more dynamic than the example i posted, i do not know how you determine the text or which part to change font or other property

westconn1,

here is (hopefully some better) code:

Sub Redden()
'
' Redden Macro
' reddens selected text with cell
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Dim myselection As Range
Set myselection = Selection
Selection.Select
With ActiveCell.Characters.Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 11
.Color = -13421773
End With
With ActiveCell.Characters.Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 14
.Color = -16776961
End With
End Sub

the problem is that is "reddens" the entire cell's content. I want to redden choice text within the cell, but not all text.

once you go into edit mode in excel, which includes selecting text within a cell, vba code will not run at all, can not even edit code within the editor

i have found a method, but it uses a custom activex dll

method select characters
press ctrl C (copy) and enter or tab to exit edit mode

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Set myclip = CreateObject("clipbrd.clipboard")
tmp = myclip.GetText
pos = InStr(Target, tmp)
If pos > 0 And Len(tmp) > 0 Then Target.Characters(pos, Len(tmp)).Font.Size = 16
    myclip.Clear
Set myclip = Nothing
End Sub

---

note this is in the worksheet change event for the worksheet

you can download and find information for the activex
http://www.vbforums.com/showthread.p...or-vba-vbs-etc

you could also use the clipboard APIs instead of the activex
there could be issues with other cells changing in rare cases, try it and see
does not work with numeric values, only text values
if you put a beep after changing font, you will know that something may have been changed, at least for testing
If pos > 0 And Len(tmp) > 0 Then Target.Characters(pos, Len(tmp)).Font.Size = 16: Beep

westconn1,

I'm not familiar with activex... your explanations make perfect sense to me; actually that is what I was suspecting was the issue.

I did try to place that code of yours into my spreadsheet, however, I'm sure I'm doing something wrong. I highlighted the text within the cell I wanted to alter, but nothing happened when I pressed tab or enter, I tried several way to activate this thing, but it's above my current skill level.

I really appreciate your help and patience. You're a true professional.

If you have the time to break it down in laymen terms I'd be willing to read however long that may be. I'm still learning this thing.

If you could include a line to change color as well so that I can see the difference from what you've already written that would be a great learning experience for me.

Thanks.

Quote:

---

If you could include a line to change color as well

---

Code:

---

If pos > 0 And Len(tmp) > 0 Then
  Target.Characters(pos, Len(tmp)).Font.Size = 16
  Target.Characters(pos, Len(tmp)).Font.color = vbred
end if

---

Quote:

---

I did try to place that code of yours into my spreadsheet, however, I'm sure I'm doing something wrong.

---

did you download and register the activex? all the instruction are in the link above
there are instructions for registering for 64 bit and vista and later os in later posts in the thread