Hello!
I found a partial answer on a locked thread here but need just a little more help to make it right for my situation.
I have a massive sheet showing data points like the below. The solution in the link above (and showing below) will change one of the * or # but not both - and not if there is additional characters after the instance of the * or #. I have to be able to have the solution continue to look at all characters in the string, and action each instance of one or both of the characters.
[TABLE="width: 177"]
<tbody>[TR]
[TD]ORIGINAL[/TD]
[TD]DESIRED[/TD]
[/TR]
[TR]
[TD]E*2#TEST10[/TD]
[TD]E*2#TEST10[/TD]
[/TR]
[TR]
[TD]E*2#STA37[/TD]
[TD]E*2#STA37[/TD]
[/TR]
[TR]
[TD]E*2#FS15[/TD]
[TD]E*2#FS15[/TD]
[/TR]
[TR]
[TD]E235*JK#123 [/TD]
[TD]E235*JK#123[/TD]
[/TR]
[TR]
[TD]E#Flk*sda#[/TD]
[TD]E#Flk*sda#[/TD]
[/TR]
</tbody>[/TABLE]
The code I'm using is below and I think the issue lies with Select Case Right(Target.CellsValue, 1) - I just have no idea how to correct this...
Thanks in advance for any and all suggestions!
I found a partial answer on a locked thread here but need just a little more help to make it right for my situation.
I have a massive sheet showing data points like the below. The solution in the link above (and showing below) will change one of the * or # but not both - and not if there is additional characters after the instance of the * or #. I have to be able to have the solution continue to look at all characters in the string, and action each instance of one or both of the characters.
[TABLE="width: 177"]
<tbody>[TR]
[TD]ORIGINAL[/TD]
[TD]DESIRED[/TD]
[/TR]
[TR]
[TD]E*2#TEST10[/TD]
[TD]E*2#TEST10[/TD]
[/TR]
[TR]
[TD]E*2#STA37[/TD]
[TD]E*2#STA37[/TD]
[/TR]
[TR]
[TD]E*2#FS15[/TD]
[TD]E*2#FS15[/TD]
[/TR]
[TR]
[TD]E235*JK#123 [/TD]
[TD]E235*JK#123[/TD]
[/TR]
[TR]
[TD]E#Flk*sda#[/TD]
[TD]E#Flk*sda#[/TD]
[/TR]
</tbody>[/TABLE]
The code I'm using is below and I think the issue lies with Select Case Right(Target.CellsValue, 1) - I just have no idea how to correct this...
Thanks in advance for any and all suggestions!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ErrHandler
Select Case Right(Target.Cells.Value, 1)
Case "#"
Target.Cells.Characters(Start:=Len(Target.Cells.Value), Length:=1).Font.Color = vbRed
Target.Cells.Characters(Start:=Len(Target.Cells.Value), Length:=1).Font.Bold = True
Case "*"
Target.Cells.Characters(Start:=Len(Target.Cells.Value), Length:=1).Font.Color = vbGreen
Target.Cells.Characters(Start:=Len(Target.Cells.Value), Length:=1).Font.Bold = True
End Select
Application.EnableEvents = True
Exit Sub
ErrHandler:
Application.EnableEvents = True
End Sub
