Solved: Nested ifs Check Changed by val

[ReallyTrying]

Hi I really need a lot of help. I took a class and now I am trying to apply what I thought I learned. Not easy thus the name Really Trying.
Please help.

= IF(ISNUMBER(H2),(H2),IF(ISNUMBER(O2),(O2-H2),""))

My question relates to the lin of code listed above.
The purpose of is to calculate Aging so I am checking for a date in both columns if there are dates entered then subtract the start date from the end date.
The formula results in an invalid name error. if I seperate the two if statements then they work there is something wrong in the nesting of the two I think. Not sure what to do next.


Also,
This code is not working properly. Once the colors are determined they dont change even as the days increment. the first line is contained in the cells the sceond is a sub associated with the worksheet. Should this be congtained in a CheckChanged mod instead?

=CONCATENATE(IF(ISNUMBER(H3),NETWORKDAYS(H3,$AA$1),"0"))


Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer, c
For Each c In Target
If Not Intersect(Range(c.Address), Range("AA1:AA1000")) Is Nothing Then
Select Case c.Value
Case 1 To 35
icolor = 27
Case 36 To 70
icolor = 26
Case 71 To 105
icolor = 44
Case 106 To 139
icolor = 45
Case 140 To 175
icolor = 46
Case 176 To 300
icolor = 3
Case Else
icolor = 0 'no color
End Select
c.Interior.ColorIndex = icolor
End If
Next c
End Sub

[Bob Phillips]

Hi I really need a lot of help. I took a class and now I am trying to apply what I thought I learned. Not easy thus the name Really Trying.

I thought it meant that you would be reall trying (to our patience)

=IF(ISNUMBER(H2),(H2),IF(ISNUMBER(O2),(O2-H2),""))

My question relates to the lin of code listed above.
The purpose of is to calculate Aging so I am checking for a date in both columns if there are dates entered then subtract the start date from the end date.
The formula results in an invalid name error. if I seperate the two if statements then they work there is something wrong in the nesting of the two I think. Not sure what to do next.

I don't get an error, but ...
You don't need to enclose things in parentheses, unless you want to change the natural evaluation order. So (O2) and (O2-H2) can just be O2 and O2-H2, which finally gives

=IF(ISNUMBER(H2),H2,IF(ISNUMBER(O2),O2-H2,""))

This code is not working properly. Once the colors are determined they dont change even as the days increment. the first line is contained in the cells the sceond is a sub associated with the worksheet. Should this be congtained in a CheckChanged mod instead?

=CONCATENATE(IF(ISNUMBER(H3),NETWORKDAYS(H3,$AA$1),"0"))

What is this trying to do, why CONCATENATE and not just =IF(ISNUMBER(H3),NETWORKDAYS(H3,$AA$1),0)

Private Sub Worksheet_Change(ByVal Target As Range)

<< snip >>

You are using a variable c, which is a range, but then geting the address and passing that to a range, a bit redundant. yOu can simplify to

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Long, c As Range
For Each c In Target
If Not Intersect(c, Range("AA1:AA1000")) Is Nothing Then
Select Case c.Value
Case 1 To 35
icolor = 27
Case 36 To 70
icolor = 26
Case 71 To 105
icolor = 44
Case 106 To 139
icolor = 45
Case 140 To 175
icolor = 46
Case 176 To 300
icolor = 3
Case Else
icolor = 0 'no color
End Select
c.Interior.ColorIndex = icolor
End If
Next c
End Sub
[/vba]
If you are trying to get the colours to change as a formula changes a value, the change event will not capture this, you need the Calculate event.

[ReallyTrying]

Xld,
Cute. I hope I dont try anyone's patience in my quest.
Thanks for the information. I have made the advised change and now the error is no longer displayed however the formula is not calculating correctely
What I was expecting was an aging value of 9 days but what I got was 38313? If I subtract the two cells without the if cell checks then I am supplied the correct value. example 11/22/04 and 12/01/04. what am I missing here. I have checked to make sure that the result is a general number and that the formating of the date cells are in the same format.

[austenr]

38313 is a date.

[Bob Phillips]

To get aging, you need both O2 and H2 ti be completed, so perhaps you should use

=IF(OR(ISNUMBER(H2),ISNUMBER(O2)),"",H2-O2)

In the other formula, drop the CONCATENATE as I showed previously.

[ReallyTrying]

Okay, this one is working properly! YOU are Golden. As you can see I have a long way to go.
Thanks again.

[ElvisFan]

Based on what you are saying, this will work.
=IF(AND(ISNUMBER(H2),ISNUMBER(O2)),(O2-H2),"")

If both fields are not numbers then it is blank. Otherwise, it does the calculation. This is a lot less complicated to me.

Elvisfan