Hi Abhishek, Jai Jinendra ! You are most welcome !
Attached is the updated solution. Please see the file and let me know if this
is the desired solution. I have added data validation in column E.
Best regards,
Ayush Jain
[Group Manager & Owner]
--- On Sun, 15/11/09, Abhishek Jain <abhishek....@gmail.com> wrote:
From: Abhishek Jain <abhishek....@gmail.com>
Subject: $$Excel-Macros$$ Re: Custom Number Format - Help >>>URGENT<<<
To: "Ayush" <jainayus...@gmail.com>
Cc: "MS EXCEL AND VBA MACROS" <excel-macros@googlegroups.com>
Date: Sunday, 15 November, 2009, 1:21 PM
Hello Ayush,
Thank you very much for the macro which worked exactly as expected. The only
problem was that I already had entries filled up, for which I used your macro
as a separate module and it solved the issue.
Although not related to this question - I am going to send this file to my
managers and I want to set a drop-down list for input values, i.e.,
0,1,2,3. Data Validation can simply do this. The problem is it will show 0 , 1
, 2 and 3 in the drop-down and when they change the value, say, from 2 to 3,
the macro will change the display from Good to Excellent. Fairly confusing for
Managers ;) I don't know, but can I somehow get an indication for display
values (Excellent, Good, Average, Poor) in the drop-down ?
Hope I am not bothering you too much. Thanks again for your suport.
Jai Jinendra
Abhishek Jain
On Sat, Nov 14, 2009 at 8:15 PM, Ayush <jainayus...@gmail.com> wrote:
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
Keep up with people you care about with Yahoo! India Mail. Learn how.
http://in.overview.mail.yahoo.com/connectmore
--
----------------------------------------------------------------------------------
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<<attachment: abhishekjain.xls>>
