Excel Formula for Negative Numbers 1

[MHadden]

Can anyone help me with the following code?
It works, but doesn't accomplish my goal.

If ActiveCell.Value < 100 Then [Code]

My goal is to find the cels that are equal to, or less than negative $100.00. I have searched this site, as well as the Excel help files but can't find the answer.

Any Help is Greatly appreciated!!

- Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[Zathras]

Without knowing more about what you intend to do when you find such cells, I could suggest using conditional formatting to highlight them in a different color.

 

[MHadden]

Thanks for taking the time to respond, however, the full code is meant to move the minus sign to the left of the number and highlight the row if any number is less than negative $100.00.

I cannot seem to figure out the proper formula for less than a negative number. Currently it works for any number less than 100 (not Negative 100). I am posting the full code here for your review. Again I thank for responding. Michael

Sub ConvertMinusThenHighlight()
Dim MemberCell As Range
For Each MemberCell In ActiveSheet.UsedRange
If Right(MemberCell.Formula, 1) = "-" Then
MemberCell.Formula = _
Val("-" & Val(Left(MemberCell.Formula, Len(MemberCell.Formula) - 1))) 'Formula for moving sign
Else
End If
Next

If ActiveCell.Value < 100 Then

Range(Selection, ActiveCell.End(xlToLeft)).Select

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid

End With

End If

End Sub

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[KenWright]

JE McGimpsey has a routine to fix trailing negatives:-

http://www.mcgimpsey.com/excel/postfixnegatives.html

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[byundt]

Michael,
Here is a revised version of your sub that looks for numbers with post-fixed - signs. It will convert them from text to normal representation, and highlight the row containing them in yellow if the value is less than -100. There is a special test for currency values (beginning with $).

Sub ConvertMinusThenHighlight()
Dim str As String
Dim MemberCell As Range
For Each MemberCell In ActiveSheet.UsedRange
    If MemberCell.HasFormula = False And Right(MemberCell, 1) = "-" Then
        If Left(MemberCell, 1) = "$" Then
            str = Trim(Mid(MemberCell, 2, Len(MemberCell) - 2))
            If IsNumeric(str) Then MemberCell = -Val(str)
            MemberCell.NumberFormat = "$#.00"
        Else
            MemberCell = -Val(Left(MemberCell, Len(MemberCell) - 1)) 'Formula for moving sign
        End If
    End If
    If MemberCell < -100 Then Range(Cells(MemberCell.Row, 1), MemberCell).Interior.ColorIndex = 6
Next
End Sub

 

[MHadden]

Amazing !!!!

Thanks to all who posted, but most especially to byundt, I have spent several hours trying & you have helped me very much!!!
THANKS, Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[MHadden]

Folks,
My boss just threw me a curve. While this does work great, he pointed out that we need to look for the "G" column only.
There will be numbers in other columns that match the criteria, but should not be highlighted.
I have attempted Column.Selected & Range (G), but can't seem to figure out what I need to do. I have searched this forum again, but all I can find is posts suggesting the "conditional formatting" from the menu bar, but I need this in code. I do still appreciate all the help, but if you could help once more, I would be grateful!!

- Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[byundt]

Michael,
To restrict the highlighting and minus sign moving to column G, change this line:
For Each MemberCell In ActiveSheet.UsedRange

To this:
For Each MemberCell In Intersect(ActiveSheet.UsedRange, Columns("G"))

If it is just the highlighting that you want to restrict to column G, then move the highlighting line into a separate loop:

For Each MemberCell In Intersect(ActiveSheet.UsedRange,Columns("G"))
    If MemberCell < -100 Then MemberCell.Interior.ColorIndex = 6
Next MemberCell

 

[MHadden]

Once again, I am in your debt.

Thank you so much!!!!

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!