Comparison Macro + In string text highlighting

[Harimau]

Hi all,

I currently have a macro that compares the differences between two
workbooks' formulae (using .formulaR1C1), and it eventually makes a list on a
new workbook in the following format:

Column 1 = first column's formulae, as a string
Column 2 = second column's formulae, also as a string.

So for example, an output for column 1 could be:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5))*VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5)

compared to column 2:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6))*VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6)

That part works smoothly enough, outputting the formula as plain black text.
However, what I want to do is to actually highlight the difference in
formulae IN the two cells by having the differences being bolded, and made
into red font. Not shading the cells, but having the text string inside the
cell being a mixture of normal black font and bold red font.

I've seen this done before, but can anyone please shed some light on these
two issues?

1) What would be the most ideal way of finding the difference between the
two formulae? If the structure of the formulae remain the same with only the
column and row numbers changing, that is easily done by comparing the
formulae character by character. However, I want it to be flexible enough to
detect it when characters are deleted/inserted.

2) How do you actually have a string in a cell that have some parts bolded
as red, while others are normal font, etc? I've seen this done before, so it
should be possible.

Thanks all!

 

[Rick Rothstein \(MVP - VB\)]

I am almost positive that you cannot mix text formatting in a cell unless
the contents of the cell is a text constant.

Rick

 

[Harimau]

What did you mean by a text constant? Is that the same just a cell
containing only text?

If so, I thought the example I posted above were text strings because of the
' in front of it. ISTEXT() returns true when referencing a cell containing
it.


--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)

I am almost positive that you cannot mix text formatting in a cell unless
the contents of the cell is a text constant.

Rick

Hi all,

I currently have a macro that compares the differences between two
workbooks' formulae (using .formulaR1C1), and it eventually makes a list
on a
new workbook in the following format:

Column 1 = first column's formulae, as a string
Column 2 = second column's formulae, also as a string.

So for example, an output for column 1 could be:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5))*VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5)

compared to column 2:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6))*VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6)

That part works smoothly enough, outputting the formula as plain black
text.
However, what I want to do is to actually highlight the difference in
formulae IN the two cells by having the differences being bolded, and made
into red font. Not shading the cells, but having the text string inside
the
cell being a mixture of normal black font and bold red font.

I've seen this done before, but can anyone please shed some light on these
two issues?

1) What would be the most ideal way of finding the difference between the
two formulae? If the structure of the formulae remain the same with only
the
column and row numbers changing, that is easily done by comparing the
formulae character by character. However, I want it to be flexible enough
to
detect it when characters are deleted/inserted.

2) How do you actually have a string in a cell that have some parts bolded
as red, while others are normal font, etc? I've seen this done before, so
it
should be possible.

Thanks all!

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I completely missed those apostrophes. I'm not exactly sure how to
approach finding the differences you asked about. Are the two formulas
always going to be the "same" except for the offset references? Will there
be other formulas that differ in structure from the examples you showed?

In the meantime, I can show you how to make portions of the text bold and
red. Assuming text in A1...

Range("A1").Characters(10, 5).Font.Bold = True
Range("A1").Characters(10, 5).Font.Color = vbRed

will make the five characters starting at the 10 character bold and red.

Rick

What did you mean by a text constant? Is that the same just a cell
containing only text?

If so, I thought the example I posted above were text strings because of
the
' in front of it. ISTEXT() returns true when referencing a cell
containing
it.


--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)

I am almost positive that you cannot mix text formatting in a cell unless
the contents of the cell is a text constant.

Rick

Hi all,

I currently have a macro that compares the differences between two
workbooks' formulae (using .formulaR1C1), and it eventually makes a
list
on a
new workbook in the following format:

Column 1 = first column's formulae, as a string
Column 2 = second column's formulae, also as a string.

So for example, an output for column 1 could be:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5))*VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5)

compared to column 2:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6))*VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6)

That part works smoothly enough, outputting the formula as plain black
text.
However, what I want to do is to actually highlight the difference in
formulae IN the two cells by having the differences being bolded, and
made
into red font. Not shading the cells, but having the text string
inside
the
cell being a mixture of normal black font and bold red font.

I've seen this done before, but can anyone please shed some light on
these
two issues?

1) What would be the most ideal way of finding the difference between
the
two formulae? If the structure of the formulae remain the same with
only
the
column and row numbers changing, that is easily done by comparing the
formulae character by character. However, I want it to be flexible
enough
to
detect it when characters are deleted/inserted.

2) How do you actually have a string in a cell that have some parts
bolded
as red, while others are normal font, etc? I've seen this done before,
so
it
should be possible.

Thanks all!

 

[Harimau]

Wow, Thanks for that! I didn't realise that was possible...

As for the formula structure, they'll be more or less the same - if they're
too different, the two cells can just be all red.

The approach I had in mind was to use delimiters - in the above example, the
most appropriate ones appear to be "[" or "]" or "R", "C", but i'm afraid I
don't have that much of an idea on how to do it this way either. Do you
think this is the right path to go to, or is there maybe a better way?


--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)

Sorry, I completely missed those apostrophes. I'm not exactly sure how to
approach finding the differences you asked about. Are the two formulas
always going to be the "same" except for the offset references? Will there
be other formulas that differ in structure from the examples you showed?

In the meantime, I can show you how to make portions of the text bold and
red. Assuming text in A1...

Range("A1").Characters(10, 5).Font.Bold = True
Range("A1").Characters(10, 5).Font.Color = vbRed

will make the five characters starting at the 10 character bold and red.

Rick

What did you mean by a text constant? Is that the same just a cell
containing only text?

If so, I thought the example I posted above were text strings because of
the
' in front of it. ISTEXT() returns true when referencing a cell
containing
it.


--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)

I am almost positive that you cannot mix text formatting in a cell unless
the contents of the cell is a text constant.

Rick


Hi all,

I currently have a macro that compares the differences between two
workbooks' formulae (using .formulaR1C1), and it eventually makes a
list
on a
new workbook in the following format:

Column 1 = first column's formulae, as a string
Column 2 = second column's formulae, also as a string.

So for example, an output for column 1 could be:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5))*VLOOKUP(R[-140]C4,Table.GST,Tables!R902C5)

compared to column 2:

'=(R[-40]C+R[-26]C+R[-4]C)/(1+VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6))*VLOOKUP(R[-264]C4,Table.GST,Tables!R902C6)

That part works smoothly enough, outputting the formula as plain black
text.
However, what I want to do is to actually highlight the difference in
formulae IN the two cells by having the differences being bolded, and
made
into red font. Not shading the cells, but having the text string
inside
the
cell being a mixture of normal black font and bold red font.

I've seen this done before, but can anyone please shed some light on
these
two issues?

1) What would be the most ideal way of finding the difference between
the
two formulae? If the structure of the formulae remain the same with
only
the
column and row numbers changing, that is easily done by comparing the
formulae character by character. However, I want it to be flexible
enough
to
detect it when characters are deleted/inserted.

2) How do you actually have a string in a cell that have some parts
bolded
as red, while others are normal font, etc? I've seen this done before,
so
it
should be possible.

Thanks all!