VBA for comparing sequences of characters.
"Ron Rosenfeld" wrote:
On 4 Dec 2006 10:19:16 -0800, "a.riva@UCL" wrote:
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
|