VBA for comparing sequences of characters.

[a.riva@UCL]

Hello!

I would like to know how can I write a VBA macro to execute the
following.
Given two strings of characters having the same length, I need a
function that compares each couple of characters in the same position
of the initial strings and gives back as a result a string equal to the
first input string but with the characters, that are different compared
with input string number two, written in bold font.

Example:

string1: A B C D E F
string2: A B C W E F

result of the formula: A B C [bold]D E F

since the first positions are both A, the second positions are both B,
the third are both C, the fifth are both E and the sixth are both F.
BUT the fourth positions differ, and so I can highlight that in string
1 there is the fourth position changed.

How can I write the code? I can easily use the Mid function to extract
the characters in each position, under a loop with "i" changing from 1
to the length of the sequences (with the Len function). But I don't
know how to change the style of the font if the character is different
between string1 and string2...

Can somebody help me?

Thank you very much!!!

Antonio Riva.

 

[ion]

Antonio,
The text format belongs to the range, not the characters. So, you'd
have to assign one character per cell.
rng.cells(lpos).value = mid(sFirst, lPos, 1)
rng.cells(lPos).Font.Bold = (mid(sFirst, lPos, 1) = mid(sSecond, lPos,
1))
hth
Ion

 

[Ron Rosenfeld]

Hello!

I would like to know how can I write a VBA macro to execute the
following.
Given two strings of characters having the same length, I need a
function that compares each couple of characters in the same position
of the initial strings and gives back as a result a string equal to the
first input string but with the characters, that are different compared
with input string number two, written in bold font.

Example:

string1: A B C D E F
string2: A B C W E F

result of the formula: A B C [bold]D E F

since the first positions are both A, the second positions are both B,
the third are both C, the fifth are both E and the sixth are both F.
BUT the fourth positions differ, and so I can highlight that in string
1 there is the fourth position changed.

How can I write the code? I can easily use the Mid function to extract
the characters in each position, under a loop with "i" changing from 1
to the length of the sequences (with the Len function). But I don't
know how to change the style of the font if the character is different
between string1 and string2...

Can somebody help me?

Thank you very much!!!

Antonio Riva.

You can only bold individual characters in a cell if the cell contains a string
(and not a function that produces a string).

So, given that A1 contains some six character string; and you want to compare
it with the string "ABCDEF", bolding the characters that are different, the
following SUB can do that:

==============================
Option Explicit

Sub BoldDiff()
Dim s1 As String
Const s2 As String = "ABCDEF"
Dim Dest As Range
Dim i As Long

Set Dest = Range("A1")
s1 = Dest.Value
ReDim Bold(1 To Len(s1))

Application.ScreenUpdating = False
For i = 1 To Len(s1)
If Mid(s1, i, 1) <> Mid(s2, i, 1) Then
Dest.Characters(i, 1).Font.Bold = True
Else
Dest.Characters(i, 1).Font.Bold = False
End If
Next i
Application.ScreenUpdating = True

End Sub
================================

Obviously, you need to add some checking to ensure the strings are, in fact, of
equal length.

Also you could get s2 from an input box, worksheet cell, or other source.
--ron

 

[Ron Rosenfeld]

The text format belongs to the range, not the characters. So, you'd
have to assign one character per cell.

Absolutely FALSE so far as the OP's question is concerned, at least in versions
of Excel since 2000. I'm not sure about '97.

He was asking about the BOLD property.

I would recommend you look up the Bold property and review the objects that it
applies to. You will discover that the Bold property applies to the Font
object; and the Font object applies to, among other things, the Characters
object, which applies to the Range collection.

I'm not aware of a "TextFormat" property of a Range. Did you have a keyword in
mind for that?

--ron

 

[LPreheim@home]

Hello!

I would like to know how can I write a VBA macro to execute the
following.
Given two strings of characters having the same length, I need a
function that compares each couple of characters in the same position
of the initial strings and gives back as a result a string equal to the
first input string but with the characters, that are different compared
with input string number two, written in bold font.

Example:

string1: A B C D E F
string2: A B C W E F

result of the formula: A B C [bold]D E F

since the first positions are both A, the second positions are both B,
the third are both C, the fifth are both E and the sixth are both F.
BUT the fourth positions differ, and so I can highlight that in string
1 there is the fourth position changed.

How can I write the code? I can easily use the Mid function to extract
the characters in each position, under a loop with "i" changing from 1
to the length of the sequences (with the Len function). But I don't
know how to change the style of the font if the character is different
between string1 and string2...

Can somebody help me?

Thank you very much!!!

Antonio Riva.

You can only bold individual characters in a cell if the cell contains a string
(and not a function that produces a string).

So, given that A1 contains some six character string; and you want to compare
it with the string "ABCDEF", bolding the characters that are different, the
following SUB can do that:

==============================
Option Explicit

Sub BoldDiff()
Dim s1 As String
Const s2 As String = "ABCDEF"
Dim Dest As Range
Dim i As Long

Set Dest = Range("A1")
s1 = Dest.Value
ReDim Bold(1 To Len(s1))

Application.ScreenUpdating = False
For i = 1 To Len(s1)
If Mid(s1, i, 1) <> Mid(s2, i, 1) Then
Dest.Characters(i, 1).Font.Bold = True
Else
Dest.Characters(i, 1).Font.Bold = False
End If
Next i
Application.ScreenUpdating = True

End Sub
================================

Obviously, you need to add some checking to ensure the strings are, in fact, of
equal length.

Also you could get s2 from an input box, worksheet cell, or other source.
--ron

 

[Ron Rosenfeld]

Hello!

I would like to know how can I write a VBA macro to execute the
following.
Given two strings of characters having the same length, I need a
function that compares each couple of characters in the same position
of the initial strings and gives back as a result a string equal to the
first input string but with the characters, that are different compared
with input string number two, written in bold font.

Example:

string1: A B C D E F
string2: A B C W E F

result of the formula: A B C [bold]D E F

since the first positions are both A, the second positions are both B,
the third are both C, the fifth are both E and the sixth are both F.
BUT the fourth positions differ, and so I can highlight that in string
1 there is the fourth position changed.

How can I write the code? I can easily use the Mid function to extract
the characters in each position, under a loop with "i" changing from 1
to the length of the sequences (with the Len function). But I don't
know how to change the style of the font if the character is different
between string1 and string2...

Can somebody help me?

Thank you very much!!!

Antonio Riva.

You can only bold individual characters in a cell if the cell contains a string
(and not a function that produces a string).

So, given that A1 contains some six character string; and you want to compare
it with the string "ABCDEF", bolding the characters that are different, the
following SUB can do that:

==============================
Option Explicit

Sub BoldDiff()
Dim s1 As String
Const s2 As String = "ABCDEF"
Dim Dest As Range
Dim i As Long

Set Dest = Range("A1")
s1 = Dest.Value
ReDim Bold(1 To Len(s1))

Application.ScreenUpdating = False
For i = 1 To Len(s1)
If Mid(s1, i, 1) <> Mid(s2, i, 1) Then
Dest.Characters(i, 1).Font.Bold = True
Else
Dest.Characters(i, 1).Font.Bold = False
End If
Next i
Application.ScreenUpdating = True

End Sub
================================

Obviously, you need to add some checking to ensure the strings are, in fact, of
equal length.

Also you could get s2 from an input box, worksheet cell, or other source.
--ron

And your point is?
--ron

 

[a.riva@UCL]

Hello!

I would like to know how can I write a VBA macro to execute the
following.
Given two strings of characters having the same length, I need a
function that compares each couple of characters in the same position
of the initial strings and gives back as a result a string equal to the
first input string but with the characters, that are different compared
with input string number two, written in bold font.

Example:

string1: A B C D E F
string2: A B C W E F

result of the formula: A B C [bold]D E F

since the first positions are both A, the second positions are both B,
the third are both C, the fifth are both E and the sixth are both F.
BUT the fourth positions differ, and so I can highlight that in string
1 there is the fourth position changed.

How can I write the code? I can easily use the Mid function to extract
the characters in each position, under a loop with "i" changing from 1
to the length of the sequences (with the Len function). But I don't
know how to change the style of the font if the character is different
between string1 and string2...

Can somebody help me?

Thank you very much!!!

Antonio Riva.

You can only bold individual characters in a cell if the cell contains a string
(and not a function that produces a string).

So, given that A1 contains some six character string; and you want to compare
it with the string "ABCDEF", bolding the characters that are different, the
following SUB can do that:

==============================
Option Explicit

Sub BoldDiff()
Dim s1 As String
Const s2 As String = "ABCDEF"
Dim Dest As Range
Dim i As Long

Set Dest = Range("A1")
s1 = Dest.Value
ReDim Bold(1 To Len(s1))

Application.ScreenUpdating = False
For i = 1 To Len(s1)
If Mid(s1, i, 1) <> Mid(s2, i, 1) Then
Dest.Characters(i, 1).Font.Bold = True
Else
Dest.Characters(i, 1).Font.Bold = False
End If
Next i
Application.ScreenUpdating = True

End Sub
================================

Obviously, you need to add some checking to ensure the strings are, in fact, of
equal length.

Also you could get s2 from an input box, worksheet cell, or other source.
--ron

Thanks a lot Ron!!!

I tried and it works perfectly!!! Now I only have to find the way to
include the Sub in the code that I already have to write the sequences,
and also I have to find the way to extend the ranges from single cells
to arrays of cells!!!

If I have any problems I'll write again here

Thanks a lot, again

Antonio.

 

[bplumhoff]

Hello,

If you just need to highlight different characters with a "special"
character:

Option Explicit

Function hldc(s1 As String, s2 As String) As String
'Highlights different characters of two strings with
'CHAR(191)="¿"
'Example: hldc("ABCDEF","ABCEDF")="ABC¿¿F"
Dim i As Long, mi As Long, ma As Long, l1 As Long, l2 As Long
Dim s As String

l1 = Len(s1)
l2 = Len(s2)
mi = l1
If mi < l2 Then
ma = l2
ElseIf mi > l2 Then
mi = l2
ma = l1
Else
ma = mi
End If

For i = 1 To mi
If Mid(s1, i, 1) = Mid(s2, i, 1) Then
s = s & Mid(s1, i, 1)
Else
s = s & "¿"
End If
Next i

hldc = s & String(ma - mi, "¿")

End Function

HTH,
Bernd

 

[Ron Rosenfeld]

Thanks a lot Ron!!!

I tried and it works perfectly!!! Now I only have to find the way to
include the Sub in the code that I already have to write the sequences,
and also I have to find the way to extend the ranges from single cells
to arrays of cells!!!

If I have any problems I'll write again here

Thanks a lot, again

Antonio.

You're welcome. Glad to help.

Take a look at the CurrentRegion and Resize properties for your "extend the
range" problem.


--ron

 

[a.riva@UCL]

Actually, the code that I've already written is the following, and it
works really good
The only thing that is missing is the code to rewrite sequence1 in the
same cell but with the characters different from sequence2 in
bold/underlined/whatever



Function SEQALIGN(sequence1 As String, sequence2 As String) As String
'It gives the consensus sequence comparing two different starting
sequences
'The repeated characters are shown as "-"
'The characters which are different in the second sequence compared
to the first one are listed

Dim length1 As Integer
Dim length2 As Integer
Dim i As Integer
Dim finalresult As String
Dim letter As String

length1 = Len(sequence1)
length2 = Len(sequence2)

If length1 <> length2 Then
MsgBox "ERROR! The two sequences MUST have the same length.
Check and make corrections."
End If

finalresult = letter

For i = 1 To length1
letter = IIf(Mid(sequence1, i, 1) = Mid(sequence2, i, 1), "-",
Mid(sequence2, i, 1))
finalresult = finalresult + letter
Next i

SEQALIGN = finalresult

'By Antonio Riva - 2006

End Function

 

[Ron Rosenfeld]

Actually, the code that I've already written is the following, and it
works really good
The only thing that is missing is the code to rewrite sequence1 in the
same cell but with the characters different from sequence2 in
bold/underlined/whatever



Function SEQALIGN(sequence1 As String, sequence2 As String) As String
'It gives the consensus sequence comparing two different starting
sequences
'The repeated characters are shown as "-"
'The characters which are different in the second sequence compared
to the first one are listed

Dim length1 As Integer
Dim length2 As Integer
Dim i As Integer
Dim finalresult As String
Dim letter As String

length1 = Len(sequence1)
length2 = Len(sequence2)

If length1 <> length2 Then
MsgBox "ERROR! The two sequences MUST have the same length.
Check and make corrections."
End If

finalresult = letter

For i = 1 To length1
letter = IIf(Mid(sequence1, i, 1) = Mid(sequence2, i, 1), "-",
Mid(sequence2, i, 1))
finalresult = finalresult + letter
Next i

SEQALIGN = finalresult

'By Antonio Riva - 2006

End Function

You won't be able to do that with a function. A function procedure returns a
value. It does not/cannot alter any properties of the cell or its contents.

I believe you will need to write a Sub procedure to accomplish your goal.
--ron

 

[a.riva@UCL]

You won't be able to do that with a function. A function procedure returns a

value. It does not/cannot alter any properties of the cell or its contents.

I believe you will need to write a Sub procedure to accomplish your goal.
--ron

Yeah... Well, I'll try to modify the code you sent me (thanks
again!!!), to taylor it to what I need

Let you know!!!

Antonio.