Hi Kevin

The reason for the error when you enter data in Column A is because you are
testing.........
".....Target.Offset(0, -2).Value 1...."
the range A2.Offset(0,-2) does not exist.

Start your code with something like.....
If Target.Column < 6 Then Exit Sub

--

XL2003
Regards

William



"Kevin Baker" wrote in message
news:zKdee.671$It1.211@lakeread02...
If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value
1 Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value - Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up front:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
Case Is = 8 'only thing left
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

The .enableevents stuff tells excel to not invoke the worksheet_change event
when the code writes to the worksheet.



Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value 1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0, 2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value - Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value 1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0, 2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value - Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the check for
numeric should be handled anymore. I'm also confused about this comment:

I want to change the color of the cell in Column G if cell F is greater than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private Sub and
wanted to get your take on how to "tweak" it. I've tried but I can't seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = "" Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is greater than
0 AND Cell H is blank.. I was copied the above code from somewhere else, so
I'm not exactly sure why the elseif statements are there. The above worked
but I would sometimes get the same error code I was having trouble with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I don't think I'd use an event for this. I'd use conditional formatting and a
formula. (It just seems simpler to me.)

Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)

If you have only one of the amounts, you'll see a yellow cell.

then in I2 (with headers in row 1???).

=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.

(H2-F2 depending on which way you want the subtraction to be)

===
Just a suggestion, I'd use another column for that indicator--it may look
prettier with the yellow, but that's difficult to work with.

I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")

Then you could apply Data|Filter|autofilter to your range and filter to show
just the missing data rows or the good rows.

===
One more thing, if you drag these formulas down, you'll be extending the print
range.

If that bothers you, apply that data|filter|autofilter and pick a column that
always has data in it.

Then filter to show non-blanks. Print the data, then data|filter|showall.

What do you think?

(and be safe!)




Kevin Baker wrote:

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change to a
yellow background. Once I enter an amount into G (when I get the vendor
invoice via US Mail), then the yellow background will turn back to nothing
(white). The purpose of the yellow background in G is to allow me to see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an amount
in F AND an amount in H and ONLY if there is a difference... I don't need to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the check
for
numeric should be handled anymore. I'm also confused about this comment:

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private Sub
and
wanted to get your take on how to "tweak" it. I've tried but I can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = ""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere else,
so
I'm not exactly sure why the elseif statements are there. The above
worked
but I would sometimes get the same error code I was having trouble with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value 1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0, 2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value - Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

Dave,

Your code was perfect.. I do have more code under the same Private Sub and
wanted to get your take on how to "tweak" it. I've tried but I can't seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = "" Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is greater than
0 AND Cell H is blank.. I was copied the above code from somewhere else, so
I'm not exactly sure why the elseif statements are there. The above worked
but I would sometimes get the same error code I was having trouble with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

Dave,

Will put what you suggest into my spreadsheet, but I can't do it until
tomorrow. I left the spreadsheet onboard the ship.. I'm currently stationed
onboard the USS Iwo Jima.. This spreadsheet will be used to replace a paper
"Purchase Order Log".

Thanks for all your help, can't wait to get back tomorrow and try it out.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the check
for
numeric should be handled anymore. I'm also confused about this comment:

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private Sub
and
wanted to get your take on how to "tweak" it. I've tried but I can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = ""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere else,
so
I'm not exactly sure why the elseif statements are there. The above
worked
but I would sometimes get the same error code I was having trouble with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change to a
yellow background. Once I enter an amount into G (when I get the vendor
invoice via US Mail), then the yellow background will turn back to nothing
(white). The purpose of the yellow background in G is to allow me to see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an amount
in F AND an amount in H and ONLY if there is a difference... I don't need to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin



"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the check
for
numeric should be handled anymore. I'm also confused about this comment:

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private Sub
and
wanted to get your take on how to "tweak" it. I've tried but I can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = ""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere else,
so
I'm not exactly sure why the elseif statements are there. The above
worked
but I would sometimes get the same error code I was having trouble with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

First, I don't think I'd copy it down all 65536 rows.

I would think doing it 20, 30, even 100 at a time would be sufficient.
(Actually, I'd just copy it down a row at a time--just when I need it.)

===
I still like the formula approach, but if you want to add the formula
automatically:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If Intersect(.Cells, Me.Range("F:F,H:H")) Is Nothing Then Exit Sub
If .Cells.Count 1 Then Exit Sub

If Me.Cells(.Row, "I").HasFormula = True Then Exit Sub

Application.EnableEvents = False
Me.Cells(.Row, "I").FormulaR1C1 _
= "=IF(COUNT(RC[-3],RC[-1])<2,""""," _
& "IF(RC[-3]=RC[-1],"""",RC[-3]-RC[-1]))"
Application.EnableEvents = True
End With

End Sub

It adds the formula the first time you put something in F or H.



Kevin Baker wrote:

Dave,

The Conditional Formatting is the perfect solutions.. awesome!

When I use the formula and copy it down (I have to copy it into the entire
row) my spreadsheet just from 68K to 3.5 meg. Is there, so I think I will
use the code for that part.

Thanks for all your help!

Kevin
"Dave Peterson" wrote in message
...
I don't think I'd use an event for this. I'd use conditional formatting
and a
formula. (It just seems simpler to me.)

Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)

If you have only one of the amounts, you'll see a yellow cell.

then in I2 (with headers in row 1???).

=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.

(H2-F2 depending on which way you want the subtraction to be)

===
Just a suggestion, I'd use another column for that indicator--it may look
prettier with the yellow, but that's difficult to work with.

I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")

Then you could apply Data|Filter|autofilter to your range and filter to
show
just the missing data rows or the good rows.

===
One more thing, if you drag these formulas down, you'll be extending the
print
range.

If that bothers you, apply that data|filter|autofilter and pick a column
that
always has data in it.

Then filter to show non-blanks. Print the data, then data|filter|showall.

What do you think?

(and be safe!)




Kevin Baker wrote:

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change to
a
yellow background. Once I enter an amount into G (when I get the vendor
invoice via US Mail), then the yellow background will turn back to
nothing
(white). The purpose of the yellow background in G is to allow me to see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an
amount
in F AND an amount in H and ONLY if there is a difference... I don't need
to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the
check
for
numeric should be handled anymore. I'm also confused about this
comment:

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private
Sub
and
wanted to get your take on how to "tweak" it. I've tried but I can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = ""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere
else,
so
I'm not exactly sure why the elseif statements are there. The above
worked
but I would sometimes get the same error code I was having trouble
with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking
up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the
worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value -
Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Glad it worked and I've sent you a private email.

Woohoo!

Kevin Baker wrote:

Dave,

It's done... works PERFECT! Thanks so much for taking the time to help me
out with this, would love to send you a IWO JIMA ballcap..

Kevin

"Dave Peterson" wrote in message
...
First, I don't think I'd copy it down all 65536 rows.

I would think doing it 20, 30, even 100 at a time would be sufficient.
(Actually, I'd just copy it down a row at a time--just when I need it.)

===
I still like the formula approach, but if you want to add the formula
automatically:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If Intersect(.Cells, Me.Range("F:F,H:H")) Is Nothing Then Exit Sub
If .Cells.Count 1 Then Exit Sub

If Me.Cells(.Row, "I").HasFormula = True Then Exit Sub

Application.EnableEvents = False
Me.Cells(.Row, "I").FormulaR1C1 _
= "=IF(COUNT(RC[-3],RC[-1])<2,""""," _
& "IF(RC[-3]=RC[-1],"""",RC[-3]-RC[-1]))"
Application.EnableEvents = True
End With

End Sub

It adds the formula the first time you put something in F or H.



Kevin Baker wrote:

Dave,

The Conditional Formatting is the perfect solutions.. awesome!

When I use the formula and copy it down (I have to copy it into the
entire
row) my spreadsheet just from 68K to 3.5 meg. Is there, so I think I
will
use the code for that part.

Thanks for all your help!

Kevin
"Dave Peterson" wrote in message
...
I don't think I'd use an event for this. I'd use conditional formatting
and a
formula. (It just seems simpler to me.)

Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)

If you have only one of the amounts, you'll see a yellow cell.

then in I2 (with headers in row 1???).

=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.

(H2-F2 depending on which way you want the subtraction to be)

===
Just a suggestion, I'd use another column for that indicator--it may
look
prettier with the yellow, but that's difficult to work with.

I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")

Then you could apply Data|Filter|autofilter to your range and filter to
show
just the missing data rows or the good rows.

===
One more thing, if you drag these formulas down, you'll be extending
the
print
range.

If that bothers you, apply that data|filter|autofilter and pick a
column
that
always has data in it.

Then filter to show non-blanks. Print the data, then
data|filter|showall.

What do you think?

(and be safe!)




Kevin Baker wrote:

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change
to
a
yellow background. Once I enter an amount into G (when I get the
vendor
invoice via US Mail), then the yellow background will turn back to
nothing
(white). The purpose of the yellow background in G is to allow me to
see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an
amount
in F AND an amount in H and ONLY if there is a difference... I don't
need
to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the
check
for
numeric should be handled anymore. I'm also confused about this
comment:

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of
posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private
Sub
and
wanted to get your take on how to "tweak" it. I've tried but I
can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) =
""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere
else,
so
I'm not exactly sure why the elseif statements are there. The
above
worked
but I would sometimes get the same error code I was having trouble
with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before
I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're
typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the
checking
up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the
worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value -
Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new
line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave,

The Conditional Formatting is the perfect solutions.. awesome!

When I use the formula and copy it down (I have to copy it into the entire
row) my spreadsheet just from 68K to 3.5 meg. Is there, so I think I will
use the code for that part.

Thanks for all your help!

Kevin
"Dave Peterson" wrote in message
...
I don't think I'd use an event for this. I'd use conditional formatting
and a
formula. (It just seems simpler to me.)

Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)

If you have only one of the amounts, you'll see a yellow cell.

then in I2 (with headers in row 1???).

=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.

(H2-F2 depending on which way you want the subtraction to be)

===
Just a suggestion, I'd use another column for that indicator--it may look
prettier with the yellow, but that's difficult to work with.

I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")

Then you could apply Data|Filter|autofilter to your range and filter to
show
just the missing data rows or the good rows.

===
One more thing, if you drag these formulas down, you'll be extending the
print
range.

If that bothers you, apply that data|filter|autofilter and pick a column
that
always has data in it.

Then filter to show non-blanks. Print the data, then data|filter|showall.

What do you think?

(and be safe!)




Kevin Baker wrote:

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change to
a
yellow background. Once I enter an amount into G (when I get the vendor
invoice via US Mail), then the yellow background will turn back to
nothing
(white). The purpose of the yellow background in G is to allow me to see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an
amount
in F AND an amount in H and ONLY if there is a difference... I don't need
to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the
check
for
numeric should be handled anymore. I'm also confused about this
comment:

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private
Sub
and
wanted to get your take on how to "tweak" it. I've tried but I can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) = ""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere
else,
so
I'm not exactly sure why the elseif statements are there. The above
worked
but I would sometimes get the same error code I was having trouble
with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the checking
up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the
worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value -
Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave,

It's done... works PERFECT! Thanks so much for taking the time to help me
out with this, would love to send you a IWO JIMA ballcap..

Kevin

"Dave Peterson" wrote in message
...
First, I don't think I'd copy it down all 65536 rows.

I would think doing it 20, 30, even 100 at a time would be sufficient.
(Actually, I'd just copy it down a row at a time--just when I need it.)

===
I still like the formula approach, but if you want to add the formula
automatically:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If Intersect(.Cells, Me.Range("F:F,H:H")) Is Nothing Then Exit Sub
If .Cells.Count 1 Then Exit Sub

If Me.Cells(.Row, "I").HasFormula = True Then Exit Sub

Application.EnableEvents = False
Me.Cells(.Row, "I").FormulaR1C1 _
= "=IF(COUNT(RC[-3],RC[-1])<2,""""," _
& "IF(RC[-3]=RC[-1],"""",RC[-3]-RC[-1]))"
Application.EnableEvents = True
End With

End Sub

It adds the formula the first time you put something in F or H.



Kevin Baker wrote:

Dave,

The Conditional Formatting is the perfect solutions.. awesome!

When I use the formula and copy it down (I have to copy it into the
entire
row) my spreadsheet just from 68K to 3.5 meg. Is there, so I think I
will
use the code for that part.

Thanks for all your help!

Kevin
"Dave Peterson" wrote in message
...
I don't think I'd use an event for this. I'd use conditional formatting
and a
formula. (It just seems simpler to me.)

Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)

If you have only one of the amounts, you'll see a yellow cell.

then in I2 (with headers in row 1???).

=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.

(H2-F2 depending on which way you want the subtraction to be)

===
Just a suggestion, I'd use another column for that indicator--it may
look
prettier with the yellow, but that's difficult to work with.

I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")

Then you could apply Data|Filter|autofilter to your range and filter to
show
just the missing data rows or the good rows.

===
One more thing, if you drag these formulas down, you'll be extending
the
print
range.

If that bothers you, apply that data|filter|autofilter and pick a
column
that
always has data in it.

Then filter to show non-blanks. Print the data, then
data|filter|showall.

What do you think?

(and be safe!)




Kevin Baker wrote:

Dave..

Here is what I want Excel to do using VBA code vice formulas:

Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any

When I enter a dollar amount into F AND H is blank, THEN G will change
to
a
yellow background. Once I enter an amount into G (when I get the
vendor
invoice via US Mail), then the yellow background will turn back to
nothing
(white). The purpose of the yellow background in G is to allow me to
see
which vendors I need an invoice from.

Okay... For Column I.. the difference is ONLY displayed if I have an
amount
in F AND an amount in H and ONLY if there is a difference... I don't
need
to
see the $0.00.

Hope this makes sense. Thanks again for all your help.

Kevin

"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:

If Target.Column = 6 And Target.Value 1

So that means we have to consider the rest.

Ouch. I just saw that .column = 7 stuff. And I'm not sure how the
check
for
numeric should be handled anymore. I'm also confused about this
comment:

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank

You seem to be checking for 1 in the code (in a few spots).

Is this close?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub

If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If

'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

=========
I don't think it's gonna work the way you want. But instead of
posting
code, it
might be better to just post what you want to do.


Kevin Baker wrote:

Dave,

Your code was perfect.. I do have more code under the same Private
Sub
and
wanted to get your take on how to "tweak" it. I've tried but I
can't
seem
to get it to work. Here is the code:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) =
""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If

I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere
else,
so
I'm not exactly sure why the elseif statements are there. The
above
worked
but I would sometimes get the same error code I was having trouble
with
before. Any ideas?

Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before
I
add/subtract, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub

If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If

If Target.Value <= 1 Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With

errHandler:
Application.EnableEvents = True

End Sub

Dave Peterson wrote:

This portion:

Target.Offset(0, -2).Value

Refers to the cell that is 2 columns to the left. If you're
typing
something
into column A (or even column B), then there's trouble.

I'm not quite sure if this fits your needs, but I'd do the
checking
up
front:

<<snipped

The .enableevents stuff tells excel to not invoke the
worksheet_change
event
when the code writes to the worksheet.

Kevin Baker wrote:

If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value -
Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If

I get the following when entering data in Cell A on a new
line:
"Run-Time error '1004'
Application-defined or object-defined error

Any thoughts?
Kevin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson