Change cell number format using IF

[sick stigma]

Is there a way to format a cell based on the value of another cell? If it were an IF statement it would be (in B1) =IF(A1="Sales","Currency format","Number format, decimal=0").
Any help would be greatly appreciated!
Sick

[Narimanych]

Hi,
Please try this one:

Please Login or Register  to view this content.

[jeffreybrown]

Hi Sick,

How about?

Please Login or Register  to view this content.

Paste code in a Sheet module

• Where to paste code
• Highlight macro to copy >> Ctrl + C >> Open your workbook
• Right click on sheet tab >> View Code >> opens the Visual Basic Editor (VBE)
• Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Now simply make a change in target cell

[Kaper]

Do you really need to do this with macro? (then probably indeed with event handler as shown above).
But remember, that you could use simple conditional formatting for that.

see the attached file (it's recycled from other thread).
I selected the C2:C13 range and used conditional formatting with format based on formula:

Please Login or Register  to view this content.

and formatting as currency EUR
Standard format for these cells was number with no decimals

[jeffreybrown]

Yes, thanks Kaper. That completely slipped my mind.

[sick stigma]

Jeff,
How does it know which cell to change (B1)?

[sick stigma]

Kaper wins!!!!!!
TYVM!
Sick

[jeffreybrown]

Hi sick,

I guess you are referring to the Change Event in post #3? If even see I made a typo.

This

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

So the target is anything in Column A. Once you make a change to anything in that column, we then offset one column to the right which would be column B.

Please Login or Register  to view this content.

This is saying, act upon the target (anything in column A) and then offset no rows (0), but one (1) column right.

[jeffreybrown]

Kaper wins!!!!!!
TYVM!
Sick

Glad this worked out for you. Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Kaper]

Thanks for marking the thread SOLVED and for reputation point.
If you haven't done so, you could also give reputation to those who answered with VBA suggestions (you posted it on Excel Programming / VBA / Macros sub-forum, so most answers were employing VBA).
As opposite to many other forums on excelforum you could grant reputation to many helpers. Moreover, you could do it even in threads not started by you, which were especially useful.