Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Macro to highlight Positive and Negative Numbers
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to highlight Positive and Negative Numbers

    Hello,
    Does anyone know of a macro that will search a column in Excel and highlight duplicates positive and negative numbers. Any assistance is greately appreciated.


    Values A
    100
    200
    300
    400
    500
    600
    700
    800
    -100
    -200
    -300
    -400
    -500
    -600
    700
    800

  2. #2
    Board Regular
    Join Date
    Oct 2012
    Posts
    187
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    You can try conditional formatting: - Select format only unique or duplicate values

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Are you trying to highlight it if there is a 100 and a -100? Or just exact duplicates?
    Quote Originally Posted by jdoggie View Post
    Hello,
    Does anyone know of a macro that will search a column in Excel and highlight duplicates positive and negative numbers. Any assistance is greately appreciated.


    Values A
    100
    200
    300
    400
    500
    600
    700
    800
    -100
    -200
    -300
    -400
    -500
    -600
    700
    800

  4. #4
    New Member
    Join Date
    Feb 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Quote Originally Posted by greenmonster15 View Post
    Are you trying to highlight it if there is a 100 and a -100? Or just exact duplicates?
    Trying to highlight if there is a 100 and a -100.
    Last edited by jdoggie; Feb 11th, 2013 at 12:33 AM.

  5. #5
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Hi jdoggie,

    Welcome to MrExcel!!

    Though conditional formatting is the way to go, the following code will also do the job:

    Code:
    Option Explicit
    Sub Macro1()
    
        'Written by Trebor76
        'Visit my website www.excelguru.net.au
    
        Dim strMyCol As String
        Dim lngStartRow As Long, _
            lngEndRow As Long
        Dim rngCell As Range, _
            rngMyData As Range
        Dim blnDupEntry As Boolean
        
        strMyCol = "D" 'Column containing the data. Change to suit.
        lngStartRow = 1 'Starting row number for the data. Change to suit.
        
        lngEndRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
        Set rngMyData = Range(strMyCol & lngStartRow & ":" & strMyCol & lngEndRow)
        
        Application.ScreenUpdating = False
        
        For Each rngCell In Range(strMyCol & lngStartRow, Range(strMyCol & Rows.Count).End(xlUp))
            blnDupEntry = Evaluate("IF(COUNTIF(" & rngMyData.Address(True, True) & "," & rngCell.Address(False, False) & ")+COUNTIF(" & rngMyData.Address(True, True) & "," & rngCell.Address(False, False) & "*-1)>=2,1,0)")
            If blnDupEntry = True Then
                rngCell.Interior.Color = RGB(255, 255, 0) 'Hightlights the duplicates in yellow. Change to suit.
            Else
                rngCell.Interior.Pattern = xlNone 'Removes any background colour as the entry is unique.
            End If
        Next rngCell
        
        Set rngMyData = Nothing
        
        Application.ScreenUpdating = True
        
        MsgBox "Process is now complete"
        
    End Sub
    Regards,

    Robert

  6. #6
    Board Regular
    Join Date
    Aug 2012
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    @jdoggie: I'm not meaning to hijack your thread.
    @Trebor76: Nice piece of code. I am going to learn so much from studying it. I think I was pretty close to getting a macro to work for jdoggie but you used a bunch of great stuff I didn't know about yet. THANKS MATE!

    Quote Originally Posted by Trebor76 View Post
    Hi jdoggie,

    Welcome to MrExcel!!

    Though conditional formatting is the way to go, the following code will also do the job:

    Code:
    Option Explicit
    Sub Macro1()
    
        'Written by Trebor76
        'Visit my website www.excelguru.net.au
    
        Dim strMyCol As String
        Dim lngStartRow As Long, _
            lngEndRow As Long
        Dim rngCell As Range, _
            rngMyData As Range
        Dim blnDupEntry As Boolean
        
        strMyCol = "D" 'Column containing the data. Change to suit.
        lngStartRow = 1 'Starting row number for the data. Change to suit.
        
        lngEndRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
        Set rngMyData = Range(strMyCol & lngStartRow & ":" & strMyCol & lngEndRow)
        
        Application.ScreenUpdating = False
        
        For Each rngCell In Range(strMyCol & lngStartRow, Range(strMyCol & Rows.Count).End(xlUp))
            blnDupEntry = Evaluate("IF(COUNTIF(" & rngMyData.Address(True, True) & "," & rngCell.Address(False, False) & ")+COUNTIF(" & rngMyData.Address(True, True) & "," & rngCell.Address(False, False) & "*-1)>=2,1,0)")
            If blnDupEntry = True Then
                rngCell.Interior.Color = RGB(255, 255, 0) 'Hightlights the duplicates in yellow. Change to suit.
            Else
                rngCell.Interior.Pattern = xlNone 'Removes any background colour as the entry is unique.
            End If
        Next rngCell
        
        Set rngMyData = Nothing
        
        Application.ScreenUpdating = True
        
        MsgBox "Process is now complete"
        
    End Sub
    Regards,

    Robert

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    @Trebor76: Nice piece of code. I am going to learn so much from studying it. I think I was pretty close to getting a macro to work for jdoggie but you used a bunch of great stuff I didn't know about yet. THANKS MATE!
    Nice of you to say so and you're welcome

  8. #8
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Hi Robert,

    What if the data was in multiple columns, say for example, it has the data in Column D to Column K, please advise.

    Thanks
    Last edited by Feroz90; Aug 1st, 2019 at 06:57 AM.

  9. #9
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Hi Ferox90,

    As long as there's only a single match for each positive and negative number this will do the job:

    Code:
    Option Explicit
    Sub Macro1()
    
        'Written by Trebor76
        'https://www.mrexcel.com/forum/excel-questions/684674-excel-macro-highlight-positive-negative-numbers.html
        
        Dim lngStartRow As Long, lngEndRow As Long
        Dim rngCell As Range, rngMyData As Range
        Dim lngCount As Long
        
        lngStartRow = 2 'Starting row number for the data. Change to suit.
        lngEndRow = Range("D:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
        Set rngMyData = Range("D" & lngStartRow & ":K" & lngEndRow)
        
        Application.ScreenUpdating = False
        
        For Each rngCell In rngMyData
            If Len(rngCell) > 0 Then
                Call HighlightOppositeSign(rngCell.Address, rngMyData.Address)
            End If
        Next rngCell
        
        Set rngMyData = Nothing
        
        Application.ScreenUpdating = True
        
        MsgBox "Process is now complete"
        
    End Sub
    Sub HighlightOppositeSign(strCellAddress As String, strDataRange As String)
    
        Dim rngCell As Range, rngMyData As Range
        Dim dblMyAmt As Double
        
        dblMyAmt = CDbl(Range(strCellAddress))
        
        For Each rngCell In Range(strDataRange)
            If rngCell.Address <> strCellAddress Then
                If rngCell.Value = dblMyAmt * -1 Then
                    If rngCell.Interior.Pattern = xlNone Then
                        Range(strCellAddress).Interior.Color = RGB(255, 255, 0)  'Hightlights the duplicates in yellow. Change to suit.
                        rngCell.Interior.Color = RGB(255, 255, 0) 'Hightlights the duplicates in yellow. Change to suit.
                        Exit For
                    End If
                End If
            End If
        Next rngCell
    
    End Sub
    Note it's best to start a new thread for each question (with a link back to the originating thread if it helps). Especially one this old.

    Robert

  10. #10
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel: Macro to highlight Positive and Negative Numbers

    Thank you Robert, it helps great.

    Sorry to ask in the same thread, since the thread is related to my query, I was continuing in it. but if at all, is there any chance of getting different colors for different amount.

    Say for example, in the below example, the 100 & -100 will get yellow, 200 & -200 will get red, and 300 & -300 will get green.

    Is it possible.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •