Dear all i want to format my cell based on the Cell value. There are 3 possible Conditions:
Cell is larger than 0.05 -> the Value should stay the same but be fromated to 0.0
Cell is smaller than a variable (called Threshold) -> the Value should be replaced with a String "a.C."
Cell is Zero -> the Value should be replaced with a Dash"
I figured out how to do this without a vavriable in here. How to add a text string .numberformat in vba is discussed here!
This:
.NumberFormat = "[=0]---;[<0.05] ""a.C."";0.0 "
Does work, while this:
Dim threshold as Double
threshold = 0.05
.NumberFormat = "[=0]---;[<threshold] ""a.C."";0.0 "
Does not work. I guess i nedd to either pass a special variable typoe to the "<" comperator or i need to escape the variable somehow?
A hint would be really appreciated! Thank you in advance
That's because threshold is part of your number format string. Basically everything that is within quotes is not touched by VBA - except for the double double quotes that tells VBA to use a quote character within the string rather than end the string.
To put the content of the variable into a string, use string concatenation. This is done in VBA using the "&" operator:
.NumberFormat = "[=0]---;[<" & threshold & "] ""a.C."";0.0 "
However, as Double can be a little bit nasty because of rounding, it's maybe better to use the format command to output it in the wanted form (eg 2 digits after the decimal). And it's usually a good idea to use an intermediate variable that you can easily check with the debugger:
Dim myNumberFormat as string
myNumberFormat = "[=0]---;[<" _
& format(threshold, "0.00") _
& "] ""a.C."";0.0 "
.NumberFormat = myNumberFormat