Using CHARACTERS on cell with formula

[JasperD]

Hi all,

I want to use the CHARACTERS in VBA on a cell that has a formula in it.
Is that possible?

Let's say I use the code below and put in A1 the value of 24 and in A2:
="A"&2*A1&"B"

Cell A2 then shows 'A48B' (as expected), but the code below does not highlight the number in red (which I expected it to do).
If I just type 'A48B' in a random cell, it does work fine. Does anyone have any bright ideas?

Please Login or Register  to view this content.

[Kevin#]

In VBA
Your string
="A"&2*A1&"B"
should be written
="A" & 2 * Range("A1").Value & "B"

I have tested this and it does highlight the number in red.

[MrShorty]

I have not yet seen a way to do that. Formatting individual characters in a cell only seems to work on text literalts, not text that is the result of a formula. If you convert the formula result to a value, then you can apply such formatting.

[JasperD]

Hi Kevin, Thanks for your reply - in VBA you're right, but as MrShorty understood correctly, the '="A" & 2 * A1 & "B"' is a formula on the sheet.

Hi MrShorty, thanks for your reply! Depressing news though

[Kevin#]

Although it may be depressing, the workaround is to have 2 worksheets

sheet1 (= working area) contains the formulas = your current set-up

sheet2 (= formatted area) contains "values" pasted from working area
- the macro would update this area when the sheet is activated (see below) or before printing etc

Please Login or Register  to view this content.

[Sc0tt1e]

Can you not remove the formula from the sheet and make the formula part of the macro then the value can be formatted?