Macro to highlight Positive and Negative Numbers

J

jdoggie

New Member
Joined
Feb 9, 2013
Messages
2
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.


[TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Values A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]300[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]400[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]600[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]800[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-100[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-300[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-400[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-600[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]800[/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Re: Excel: Macro to highlight Positive and Negative Numbers

You can try conditional formatting: - Select format only unique or duplicate values
 
Upvote 0
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?
jdoggie said:
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.


[TABLE="width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Values A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]300[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]400[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]600[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]800[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-100[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-300[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-400[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-600[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]800[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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
 
Upvote 0
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!

Trebor76 said:
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
Click to expand...
 
Upvote 0
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!
Click to expand...

Nice of you to say so and you're welcome ;)
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Data123
  • Solved
Conditional Formatting Positive & Negative Number
2
Replies
16
Views
3K
Data123
Data123
R
  • Question Question
IF Function Common To Work with multiple combination of two negative & positive numbers
Replies
5
Views
288
Micron
M
J
  • Solved
Sumifs - Using Multiple Criteria and Multiple Values
Replies
7
Views
307
NickFru
N
C
  • Solved
Counting Unique Numbers
Replies
3
Views
636
cotech_10
C
T
  • Solved
Categorizing and adding data with multiple conditions
Replies
5
Views
514
kevin9999
K

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top