Highlight the contents using VBA or Macro.

**excelrakesh** · 03-11-2011, 06:12 AM

Problem Statement:
Let us say, I have following columns and their respective values.
A3: Hello
A4: Excel
A5: World

A7: =A3&A4&A5

The output of this formula for example will be displayed in A7 as.

HelloExcelWorld

Now let us say, in a range of cells, there are many formula and some of them refer to contents in A7.

For example, many cells have complex formula and within the formula they have a call to MID function like =MID (A7, 6, 5), which is the substring “Excel” from column A7.

What I want is some way to highlight the substring “Excel” in the actual column i.e. A7.

Contents in A7 should show Excel in Bold Blue colour, and remaining string as is.

So, basically I need some VBA code or macros to find out what all other cells refer contents of A7 and then highlight the contents in A7, which are being referred.

Any help in this regard is highly appreciated.

**JBeaucaire** · 03-11-2011, 10:17 PM

It is not possible to partially format the text result display of a formula. You can only partially format text that is flat text in a cell. Even with VBA. (or a macro, hehe)

**pike** · 03-11-2011, 10:33 PM

Hi excelrakesh
Try this with xl2003
not to sure when the characters syntax was introducted

Please Login or Register  to view this content.

**excelrakesh** · 03-12-2011, 09:03 AM

First of all thanks for your replies. This is definitely possible for sure. Infact, I have seen an example in some sheet, but it was bit different.

The main thing is I don't know what all other cells refer A7, so reply by pike will not apply as the logic mentioned by him assumes that it is already known what all cells refer A7, whereas the biggest problem I have is I don't know what cells refer A7.

So I need 2 things, first to check which all cells refer to A7, using MID() function. And then, highlight the contents within the cell that are being used.

Please suggest some solution.

**JBeaucaire** · 03-12-2011, 10:04 AM

I repeat. If the cells you are talking about contain formulas, then you cannot partially format the text displayed as a result of the formulas.

**excelrakesh** · 03-14-2011, 06:49 AM

Thanks for your reply.

I got your point. What I can do is copy the result of the formula in a new Cell, and then highlight contents in that cell. I am sure this is possible.

Let us assume that the location of the cell where I copy the result of the formula is fixed. For example A9 i.e. I have pasted result of formula in A7 in A9. So I want to highlight contents in A9.

Now, how to reach till there, assuming rest of the problem statement is same.

**excelrakesh** · 03-14-2011, 07:21 AM

To explain and reduce the problem statement further. I want to get to the following point.

If am able to produce following data using some logic i.e.
Column Being referred StartPosition NumberOf characters
A7 6 5

Then I use this information to highlight text in A9.

**JBeaucaire** · 03-14-2011, 12:14 PM

You can use the INSTR() function in VBA to get the "position" of one string inside another.
You can use the LEN() function in VBA to get the length of a particular string.

The INSTR() result could be used as the "starting point" parameter.
The LEN() result could then be used as the "number of characters" parameter.