Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))

If you wish to use that as a worksheet formula, try it this way:
=IF(ISBLANK(A1),"e",IF(A1=1,"a",IF(A1<1,"b",IF(A1 1,"c","d"))))

You need to test the ISBLANK early because if you do not then Excel
interprets it as 0 and the A1<1 test ends up being true before you finish
testing within the nested IF statements.

"FARAZ QURESHI" wrote:

Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))

By the way, I do not think you will ever see "d" as a result, if it is blank,
then it will display "e", otherwise it is not blank and the contents will be
interpreted as a value and the =1, <1 and 1 tests will capture it.

"FARAZ QURESHI" wrote:

Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))

Before you think about macros, you'll need to sort out your IF logic.

After you've tested for =1 and <1, you don't need to test for 1, as that's
all it can be (so you can't get to d or e).
Also, if you want to test for blanks, you'll need to do that before you
check the values; a blank will currently give a b output, as blank is less
than 1.
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))

Thanx David & JLatham,

But it was actually an example only.

Problem is that I have actually more than 20 criterias to be checked and I
have come to know that I won't be allowed to insert more than 7 nested
functions.
I don't think there would be any other wayout but a looping macro.

Please guide me in respect of how to write up such a macro to check the
criterias like , <, =, < and isblank. I would also appreciate if you would
guide me as to how a conditional formatting may also be added upon the
criteria not being met.

Lets have another example like:

How to:

Have Cell B1 be Red If A1<0;
Have Cell B1 be Bold If A10;
Have Cell B1 be Underline If A1=0;
Have Cell B1 be Italic If A1<0; or
Have Cell B1 be showing "X" If A1 is blank;

"David Biddulph" wrote:

Before you think about macros, you'll need to sort out your IF logic.

After you've tested for =1 and <1, you don't need to test for 1, as that's
all it can be (so you can't get to d or e).
Also, if you want to test for blanks, you'll need to do that before you
check the values; a blank will currently give a b output, as blank is less
than 1.
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))

I'll show a simple, one cell case using the Worksheet_Change() event to deal
with your problem. To insert the code below, right-click on the worksheet's
name tab and choose [View Code] from the list and put the code into the
module that appears in the Visual Basic editor. This shows changes to B1
being made in response to changes in value being made in A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
'same as ISBLANK:
If IsEmpty(Target) Then
Range("B1") = "X"
Exit Sub
End If
'start these tests with
'the smallest limit and
'work upward in value
'first reset any previous results
Range("B1").Interior.ColorIndex = xlNone
With Range("B1").Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Bold = False
.Italic = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Select Case Target.Value
Case Is = 0
'underline text
Range("B1").Font.Underline = xlUnderlineStyleSingle
Case Is < -20
Range("B1").Font.Underline = xlUnderlineStyleSingle
Range("B1").Interior.ColorIndex = vbRed
Case Is < -10
Range("B1").Font.Underline = xlUnderlineStyleSingle
Range("B1").Interior.ColorIndex = vbYellow
Case Is < 0
Range("B1").Interior.ColorIndex = vbRed
Case Is < 10
Range("B1").Font.Bold = True ' BOLD
Range("B1").Font.Italic = True
Case Else
'any left over condition, or
'does not meet any criteria
'you do not have to have any
'code in this section
End Select
End Sub



"FARAZ QURESHI" wrote:

Thanx David & JLatham,

But it was actually an example only.

Problem is that I have actually more than 20 criterias to be checked and I
have come to know that I won't be allowed to insert more than 7 nested
functions.
I don't think there would be any other wayout but a looping macro.

Please guide me in respect of how to write up such a macro to check the
criterias like , <, =, < and isblank. I would also appreciate if you would
guide me as to how a conditional formatting may also be added upon the
criteria not being met.

Lets have another example like:

How to:

Have Cell B1 be Red If A1<0;
Have Cell B1 be Bold If A10;
Have Cell B1 be Underline If A1=0;
Have Cell B1 be Italic If A1<0; or
Have Cell B1 be showing "X" If A1 is blank;

"David Biddulph" wrote:

Before you think about macros, you'll need to sort out your IF logic.

After you've tested for =1 and <1, you don't need to test for 1, as that's
all it can be (so you can't get to d or e).
Also, if you want to test for blanks, you'll need to do that before you
check the values; a blank will currently give a b output, as blank is less
than 1.
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
Can some1 pls give me an idea of how to design a simple "4" step looping
structure macro e.g.

If(A1=1,"a",if(a1<1,"b",if(a11,"c",if(not(isblank (a1)),"d","e"))))