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