Hi Abhishek,
Unfortunately, The custom number format can take only up to two
conditions and you have three conditions in your requirement. Also,
Conditional formatting may not work as it displays colors,borders and
patterns.
I have written a macro for you which should fulfill your requirement.
This macro should be used in Change event of excel sheet(or workbook)
and you should specify your desired range in macro. So, whenever you
will change value in the range, the macro will trigger and change the
custom format.
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In ActiveSheet.Range("A1:Z100")
If cell.Value < 2 Then
cell.NumberFormat = "[=0]""Poor"";[=1]""Average"""
End If
If cell.Value > 1 And cell.Value < 4 Then
cell.NumberFormat = "[=2]""Good"";[=3]""Excellent"""
End If
Next
End Sub
I hope this macro will help you. Let us know if it does not work or
any further help is required.
Best regards,
Ayush Jain
[Group Owner and Manager]
On Nov 14, 1:52 pm, Abhishek Jain <abhishek....@gmail.com> wrote:
> Hi folks,
>
> I have this cell where the values filled can be - 0 or 1 or 2 or 3. I want
> to set a custom format for excel to show -
>
> "Poor" for 0
> "Average" for 1
> "Good" for 2
> "Excellent" for 3
>
> Now, I've tried and figured out that I can set maximum 03 conditions in
> Custom Number format but in my case they are 4. I used this format -
>
> [=0]"Poor";[=1]"Average";"Good"
>
> It's working for 03 conditions but If I add one more condition in this, -
> error ! format can't be set.
>
> I tried this formula in Conditional Formatting, which is working in another
> cell but not in formatting -
>
> =IF(J2=0,"Poor",IF(J2=1,"Average",IF(J2=2,"Good","Excellent")))
>
> Any help is greatly appreciated.
>
> Thanks,
>
> AJ
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
