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
