Custom Number Format can only display 3 Conditional formats using the [ ] parameters

[Linus100]

Is there a way do to 5 conditional formats using VB

[JBeaucaire]

You're using Excel 2010. You can apply as many CF formats as you want, by hand or by VBA.

[Linus100]

I need help with code. I need to write code for:

[=1]"Full";[=2]"Ltd";[=3]"None";[=4]"NS"; "Closed"

[JBeaucaire]

Ah, custom number format, not conditional formatting. My apologies.

Custom number formats allow 4 designations and you can't change what they do:

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
http://office.microsoft.com/en-us/ex...010342372.aspx


You could create vba to watch specific cells and convert numbers into words or vice versa.

There's even a Data Validation trick that would give you a drop down with words, but when made a choice a numeric code would appear instead, again with no VBA. That doesn't work the other way around, though.

[Linus100]

I am a novice a writing code. Can you help? I have attached the spreadsheet.

[JBeaucaire]

Linus, it sounds like you want to change the number 1 to display as "FULL" in the original cell, 2 = "LTD", etc. 5 different changes, yes?

I have to at least try to steer you way from VBA. Why not simply use a formula in an adjacent cell to add this new text value as a new datapoint?

[JosephP]

why can't you use conditional formats instead of number formats?

[JBeaucaire]

JosephP - Custom Number format can physically change the appearance of numbers in a cell, including adding text that isn't really there. It affects numeric display. Conditional Format affect the display characteristics of the cell based on the content of cell values. OK?

[Linus100]

I know the difference. I am not looking for a conditional format. I need to change the display. If there is a one in the cell, I want it to display full, if there is a 2 I want it to display Ltd. I will need a vb code because I need to go up to 5.

[JBeaucaire]

I'll let Joseph pursue the number formats trick, which might be awesome when done.

Here's a macro to do what you've outlined, select a range of cells first, then run the macro:

Option Explicit

Sub ReplaceNumbersWithText()
Dim cell As Range

For Each cell In Selection
    Select Case cell.Value
        Case 1
            cell.Value = "Full"
        Case 2
            cell.Value = "Ltd"
        Case 3
            cell.Value = "None"
        Case 4
            cell.Value = "NS"
        Case 5
            cell.Value = "Closed"
    End Select
Next cell

End Sub

[JosephP]

JBeaucaire-conditional formats can include custom number formats. ok? ;-)

[JBeaucaire]

JBeaucaire-conditional formats can include custom number formats. ok? ;-)

So maybe stacking the numeric custom number formats inside several conditional formats that are each filtering for a single value, that's nice! Great idea.

[Linus100]

I will try it

[JosephP]

yeah-and apply "Closed" as the default number format for the field

[JosephP]

@JBeaucaire-did you see it's a pivot table?

[JBeaucaire]

@JBeaucaire-did you see it's a pivot table?

No, the file was called Test Sites, so Sites was the only thing I was looking at.

[Linus100]

yes this is for a pivot table

[JBeaucaire]

I'm out then...