Cell evaluation problem

Folks,
The below code appears not to be working as in other worksheets. When I check the value in C6 it is $107.00, however, the code does not goto the Else statement. Attached is worksheet.
Application.Calculation = xlCalculationManual
    'On Error GoTo errMsg
    If Range("C6").Text <> "$107.00" Then
        'Range("D6").Locked = False
        Range("D6").Font.Color = vbWhite
        Range("D6").Interior.Color = vbRed
        Range("D6").Value = "Error"
        'CheckFormulaFunction
        Exit Sub
    Else
        Range("D6").Value = ""
        Range("D6").Font.Color = vbBlack
        Range("D6").Interior.Color = vbWhite
        'Range("D6").Locked = True
    End If

Open in new window

Problem-with-IF.xlsm
Frank FreeseAsked:
Who is Participating?
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.

Steven HarrisPresidentCommented:
Apparently we have an issue here.  When I recreate your workbook on my own and run this, it runs fine.  In your workbook, I have to change the code to:

Application.Calculation = xlCalculationManual
    'On Error GoTo errMsg
    If Range("C6").Value2 <> "107" Then
        'Range("D6").Locked = False
        Range("D6").Font.Color = vbWhite
        Range("D6").Interior.Color = vbRed
        Range("D6").Value = "Error"
        'CheckFormulaFunction
        Exit Sub
    Else
        Range("D6").Value = ""
        Range("D6").Font.Color = vbBlack
        Range("D6").Interior.Color = vbWhite
        'Range("D6").Locked = True
    End If

Open in new window


Notice the changes on line 3...
0
Frank FreeseAuthor Commented:
ThinkSpaceSolutions,
It does not matter what I do, change it to Value2 or leave it as Text the same problem. I'm believe there's a problem with the file being corrupted (how does one recover from that?) or my Excel needs to be reinstalled.
0
byundtMechanical EngineerCommented:
The format chosen for cell C6 puts a single space after the value: "$107.00 ". With this in mind, you could alternatively make your code work by changing statement 3 to:
 If Range("C6").Text <> "$107.00 " Then
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Frank FreeseAuthor Commented:
byundt,
In the code I posted in my question, that's what I had.
0
byundtMechanical EngineerCommented:
fh_freese,
Look very carefully at your original code and what I suggested as a correction. There is a trailing space in my correction.
If Range("C6").Text <> "$107.00" Then            'This is your original code
If Range("C6").Text <> "$107.00 " Then          'This is my corrected version. Note the space after $107.00

Open in new window

Although cell B9 looks as though it uses the same format, it does not include the trailing space.

You can examine exactly which format is being used by selecting the cell and opening the Custom format menu item.
$#,##0.00_);[Red]($#,##0.00)         format used for cell C6
$#,##0.00             format used for cell B9
0

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
byundtMechanical EngineerCommented:
FWIW, I caught the problem by stepping through the code using F8. When I hovered the cursor over Range("C6").Text I saw the space character inside the double quotes. That made the diagnosis simple.

If you copy the format from cell B9 and apply it to cell C6, then your original code would work.

Brad
0
Frank FreeseAuthor Commented:
Brad,
You're correct but how in the blazes did the trailing space get there? I see the problem now but I can't image the extra space.
0
Frank FreeseAuthor Commented:
I might have found it. When I right aligned the cell, not extra space.
0
Saqib Husain, SyedEngineerCommented:
The space comes from the currency format which reserves a space for the closing brace in case the number is negative
0
Frank FreeseAuthor Commented:
thanks folks - have a great evening now
0
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
Microsoft Excel

From novice to tech pro — start learning today.