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]
 
Sort by date Sort by votes
Re: Excel: Macro to highlight Positive and Negative Numbers

What about if there's more than three matching positive and negative numbers?
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Re: Excel: Macro to highlight Positive and Negative Numbers

Yes, we may have a chance of getting same numbers, but it is OK, if it applies the same color for those.
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

So what is supposed to happy if there's more than three matches? You said yellow for first match 100 v -100, red for second 200 v -200 and green for third 300 v -300. What other matches like 400 v -400, 500 v -500 etc? Do you mean to start back to yellow again?
 
Last edited:
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Sorry for the late response.

It may sound odd, but I need at least 10 different colors first, then we may start back with the same list of colors.

100 v -100 - Yellow
200 v -200 - Red
300 v -300 - Green
400 v -400 - Blue
500 v -500 - Orange
600 v -600 - Pink
700 v -700 - Violet
800 v -800 - Black
900 v -900 - Magenta
1000 v -1000 - Cyan

Thank you so much for your effort Robert.
 
Last edited:
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Try this:

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 lngMyCount 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 And rngCell.Interior.Color = 16777215 Then
            If lngMyCount > 10 Then
                lngMyCount = 1
            Else
                lngMyCount = lngMyCount + 1
            End If
            Call HighlightOppositeSign(rngCell.Address, rngMyData.Address, lngMyCount)
        End If
    Next rngCell
    
    Set rngMyData = Nothing
    
    lngMyCount = 0
    
    Application.ScreenUpdating = True
    
    MsgBox "Process is now complete"
    
End Sub
Sub HighlightOppositeSign(strCellAddress As String, strDataRange As String, lngMyCount As Long)

    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.Color = 16777215 Then
                    Select Case lngMyCount
                        Case Is = 1 'Yellow for 1st match
                            Range(strCellAddress).Interior.Color = RGB(255, 255, 0)
                            rngCell.Interior.Color = RGB(255, 255, 0)
                            Exit For
                        Case Is = 2 'Red for 2nd match
                            Range(strCellAddress).Interior.Color = RGB(255, 0, 0)
                            rngCell.Interior.Color = RGB(255, 0, 0)
                            Exit For
                        Case Is = 3 'Green for 3rd match
                            Range(strCellAddress).Interior.Color = RGB(0, 128, 0)
                            rngCell.Interior.Color = RGB(0, 128, 0)
                            Exit For
                        Case Is = 4 'Blue for 4th match
                            Range(strCellAddress).Interior.Color = RGB(0, 0, 255)
                            rngCell.Interior.Color = RGB(0, 0, 255)
                            Exit For
                        Case Is = 5 'Orange for 5th match
                            Range(strCellAddress).Interior.Color = RGB(255, 165, 0)
                            rngCell.Interior.Color = RGB(255, 165, 0)
                            Exit For
                        Case Is = 6 'Pink for 6th match
                            Range(strCellAddress).Interior.Color = RGB(255, 192, 203)
                            rngCell.Interior.Color = RGB(255, 192, 203)
                            Exit For
                        Case Is = 7 'Violet for 7th match
                            Range(strCellAddress).Interior.Color = RGB(238, 130, 238)
                            rngCell.Interior.Color = RGB(238, 130, 238)
                            Exit For
                        Case Is = 8 'Black for 8th match. Note changed the font to white or else you won't see the number
                            With Range(strCellAddress)
                                .Interior.Color = RGB(0, 0, 0)
                                .Font.Color = RGB(255, 255, 255)
                            End With
                            With rngCell
                                .Interior.Color = RGB(0, 0, 0)
                                .Font.Color = RGB(255, 255, 255)
                            End With
                            Exit For
                        Case Is = 9 'Magenta for 9th match
                            Range(strCellAddress).Interior.Color = RGB(255, 0, 255)
                            rngCell.Interior.Color = RGB(255, 0, 255)
                            Exit For
                        Case Is = 10 'Cyan for 10th match
                            Range(strCellAddress).Interior.Color = RGB(0, 255, 255)
                            rngCell.Interior.Color = RGB(0, 255, 255)
                            Exit For
                    End Select
                End If
            End If
        End If
    Next rngCell

End Sub
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Hey Robert, It works, I really appreciate your effort. It is been a great pleasure.

Thank you for your efforts again.

If at all I need to learn all such things, how to learn it. Is there any material for learning all these stuffs. Please let me know.

Thank you again
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Here is another approach you may wish to consider. It is more compact and also has a lot less looping. I don't know how big your data is but in my testing this was also considerably faster.
BTW, I also changed black to grey in the list of colours so that the font colour did not also need to be changed for those cells.

Code: 
Sub HighlightOpposites()
  Dim myColors As Variant
  Dim rData As Range, cell As Range, rFound As Range
  Dim lr As Long, k As Long

  Const fr As Long = 2 '<- First row of data
  
  myColors = Split("27 3 4 32 45 38 29 16 26 8")
  lr = Range("D:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
  Set rData = Range("D" & fr, Range("K" & lr))
  Application.ScreenUpdating = False
  For Each cell In rData
    If Not IsEmpty(cell.Value) Then
      If Left(cell.Value, 1) <> "#" Then
        Set rFound = rData.Find(What:=-cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rFound Is Nothing Then
          Union(cell, rFound).Interior.ColorIndex = myColors(k)
          cell.Value = "#" & cell.Value
          rFound.Value = "#" & rFound.Value
          k = k + 1
          If k > UBound(myColors) Then k = 0
          Set rFound = Nothing
        End If
      End If
    End If
  Next cell
  rData.Replace What:="#", Replacement:="", LookAt:=xlPart
  Application.ScreenUpdating = True
  MsgBox "Done"
End Sub
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Hi Feroz90,

Thanks for letting us know and you're welcome. I'm glad we got there in the end :)

Make sure to try Peter_SSs solution as it looks pretty nifty and he would have spent some time putting it together.

Regards,

Robert
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

I've tweaked the code slightly & also marked where you could easily add more colours if you want.
See here for a list of the available ColorIndex values & colours

Rich (BB code): 
Sub HighlightOpposites_v2()
  Dim myColors As Variant
  Dim rData As Range, cell As Range, rFound As Range
  Dim lr As Long, k As Long

  Const fr As Long = 2 '<- First row of data
  
  myColors = Split("27 3 4 32 45 38 29 16 26 8") '<- If want more colours add them here.
  lr = Range("D:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
  Set rData = Range("D" & fr, Range("K" & lr))
  Application.ScreenUpdating = False
  For Each cell In rData
    If IsNumeric(cell.Text) Then
      Set rFound = rData.Find(What:=-cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
      If Not rFound Is Nothing Then
        Union(cell, rFound).Interior.ColorIndex = myColors(k)
        cell.Value = "#" & cell.Value
        rFound.Value = "#" & rFound.Value
        k = k + 1
        If k > UBound(myColors) Then k = 0
        Set rFound = Nothing
      End If
    End If
  Next cell
  rData.Replace What:="#", Replacement:="", LookAt:=xlPart
  Application.ScreenUpdating = True
  MsgBox "Done"
End Sub
 
Upvote 0
Re: Excel: Macro to highlight Positive and Negative Numbers

Great Work Robert and Peter. Thank you both for your kindness.

It is been pleasure to have guys like you.

Thanks
 
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
295
Micron
M
J
  • Solved
Sumifs - Using Multiple Criteria and Multiple Values
Replies
7
Views
351
NickFru
N
C
  • Solved
Counting Unique Numbers
Replies
3
Views
663
cotech_10
C
T
  • Solved
Categorizing and adding data with multiple conditions
Replies
5
Views
521
kevin9999
K

Forum statistics

Threads
1,224,829
Messages
6,181,224
Members
453,025
Latest member
Hannah_Pham93

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