TWO IDENTICAL COLUMNS IN EXCEL - DIFFERENT RESULTS

A couple of nights ago, with the help of several of you, I built a journal entry sheet with some very helpful formatting.  There is a column in each worksheet with identical parameters. Same special number format, same spacing and both controlled by the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    'modify the range A1:A20 to match your input cells
    If Not Intersect(Target, [J8:J46]) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
        Application.EnableEvents = True
    End If
End Sub

In the attached workbook, Tab JE Page 1, the column is J8..J46.  You enter say 12 and Enter and the cell is formatted 1200-00-00. If you hit Delete, the cell clears to blank!
In the attached workbook, Tab JE Page 2, the column is H8..J48.  You enter say 12 and Enter and the cell is formatted 1200-00-00. If you hit Delete, THE CELL CLEARS TO 0000-00-00!

Why one cell clears to blank (as it should) and another clears to 0000-00-00 (as it should not) is beyond me.  Everything about these two cells are identical, except the outcome.
There must be something different that just I am not seeing.
New-Journal-Entry-TEST--RBASEGOE10GA-.xl
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
They are not identical. In page 1 columns J, K and L are merged and the code exits at

  If Target.Cells.Count > 1 Then Exit Sub

because 3 cells are selected.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
HI,

You could use this format as workaround ( to hide zero)
0000-00-00;;""

Regards
Saqib Husain, SyedEngineerCommented:
Or you can change your line to

        If Target.Value <> "" Then Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Saqib Husain, SyedEngineerCommented:
On page 1 the macro is actually not working. To make it work you can change your line to

    If Target.Rows.Count > 1 Then Exit Sub
Bill GoldenExecutive Managing MemberAuthor Commented:
This makes NO sense to me.  Page 1 is working exactly as intended.  I am not sure I understand the VBA code, but nonetheless, it is working as I had wanted it to.  Therefore, how do I get Page 2 to NOT WORK the same way?
Saqib Husain, SyedEngineerCommented:
As I mentioned above change your line to

        If Target.Value <> "" Then Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
Bill GoldenExecutive Managing MemberAuthor Commented:
Do I change the line in both places or just the code associated with Page 2?
Saqib Husain, SyedEngineerCommented:
As I said, the code is not working on page 1 (only when you delete the entry). So at this point it does not matter whether you change it for page 1 or not.

You do have to do it for page 2.
Bill GoldenExecutive Managing MemberAuthor Commented:
IT worked.  Thank you very much.  Before I close the question, let me see if I understand: In the future I would use your code when the cell is comprised of only one column?
Saqib Husain, SyedEngineerCommented:
You can put it that way.
Bill GoldenExecutive Managing MemberAuthor Commented:
Problems solved...Mission accomplished. Thanks guys.  Now it is on to the next project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.