Show Plus Sign On Numbers Greater Than X

[Andy Wild]

I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10"

Please let me know. Thanks.

[AAE]

Wrong move. It will change the number into a text string and you can no longer use the number for calculations without going to trouble to strip off the "+" sign.

Why do you need this?

ADDED BY ADMIN

Custom Format the cells as;

[>9]+General;General

[Andy Wild]

my worksheet looks like this:
column A s m l xl
men's jacket 100 150 200 9

could it say: >10 >10 >10 9

[AAE]

Post a sample workbook and we'll take a look at it.

[Andy Wild]

here is a sample:

S M L XL XXL

COMP ZIP JKT CR 40 57 67 58 10
COMP ZIP JKT GR 4 -5 7 14 3

[AAE]

Posting a sample workbook means to upload a copy. File size is limited to 48K.

When posting scroll down a bit until you see an "Additional Options" section that lets you manage attachments. You should be able to take it from there.

[Andy Wild]

Please let me know if you can open the following Apparel ATS spreadsheet. Thanks.
Apparel ATS.xls

[AAE]

I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10".
I don't want my customers to know that I have, say 100 of an item. We just want to let them know we have, say enough product to fill their order

Unless you're required to let them view your inventory records, can't you just confim to them you have the stock on-hand to meet their order requirements?

I assume you are refering to the totals column. This will place a "10+" in the column next to the totals.

Sub Show10() 
     
    Dim c As Range 
     
    With Sheet1 
         
        For Each c In Range("J5", Range("J65536").End(xlUp)) 
            If c.Value > 10 Then 
                c.Offset(0, 1).Value = "10+" 
            End If 
        Next c 
         
    End With 
     
End Sub 

[Andy Wild]

actually, we need the individual sizes to show the 10+ too (in addition to the totals)
thanks

[AAE]

Replace the code in the workbook with this:

Sub Show10() 
     
    Dim c As Range, c2 As Range 
     
    With Sheet1 
         
        For Each c In Range("J5", Range("J65536").End(xlUp)) 
            If c.Value > 10 Then 
                c.Offset(0, 1).Value = "10+" 
            End If 
        Next c 
         
        For Each c2 In Range("D3", Range("D3").End(xlToRight)) 
            If c2.Value > 10 Then 
                c2.Offset(1, 0).Value = "10+" 
            End If 
        Next c2 
         
         
    End With 
     
End Sub