Solved: Problem in using Custom Format through VBA

[msuresh]

Hi,

I am trying to change a cells display format through custom format option in a workbook. It works like this --a macro will change the target cell's display based on adjacent cells value.

In attached model in Cell “H9” has a value of 2000. Its adjacent cell has a text as “LTM”. By using a macro code I want to change the display format of H9 as LTM. But macro is taking “M” in “LTM” as month for the year 2000 and changing display format as “LT6”.

Please help me how to avoid excel reading “M” as month.

Advance Thanks,
Suresh

[Bob Phillips]

There is nop suh format as LTM that I am aware of. How do you want H9 to show as?

[msuresh]

Hi Xld,

I want to show Cell H9 as "LTM" or "FY"

[Bob Phillips]

[vba]
Sub Custom_Format()

Dim x As String
Dim rng As Range

Set rng = Range("H9")

x = """" & rng.Offset(0, 1).Value & """"
rng.NumberFormat = x

rng.Select
End Sub
[/vba]

[msuresh]

Thanks Xld, It is working fine.

[msuresh]

How can name this thread as "solved". I am not seeing any buttons.

[Bob Phillips]

Above the threads is a Thread Tools dropdown. It is there.