Setting number/currency format by formula (for example TEXT formula) and calculation error

Hi all,

Background: Currency/number format set by selected currency (B8:B12) and number format set accordinly (C8:C12)
Values in several sheets and the values should be shown based on selected currency & format.

Question, how to set format the way numbers can be calculated? Calculating cells with TEXT formula causes error, possibly without VBA.

Conditional format is one solution, but all cells should be formatted by using format painter, but that applies cell color etc, not just the conditional format by formula to set the number format.

Any ideas?