help with search and mark ,vba.

[avishain]

hello there,

im using excel 2007.

i got a pivot table from column A to G.

in that pivot table,column G consist of numbers,some are positive and some are negative.

i want to create a macro that will do the following:

i want it to take the number on the selected cell (column G) and find the oposite value (if i chose the cell which have 60 in it,i want to excel to find -60 ,if i chose -2,i want excel to find me the number 2)

so like i said,i want to color all the appropriate matches in red.

can anyone help?

thanks

 

[AlphaFrog]

Sub Highlight()
    Dim Found As Range, FirstFound As String
    Set Found = Range("A:G").Find(-ActiveCell.Value, , xlValues, xlWhole)
    If IsNumeric(ActiveCell.Value) Then
        If Not Found Is Nothing Then
            FirstFound = Found.Address
            Do
                Found.Interior.ColorIndex = 3   'Red
                Set Found = Range("A:G").FindNext(Found)
            Loop Until Found.Address = FirstFound
        Else
            MsgBox "No signed-complement match found for " & ActiveCell.Value
        End If
    End If
End Sub

Sub Highlight_Clear()
    Range("A:G").Interior.ColorIndex = xlNone
End Sub

 

[avishain]

Sub Highlight()
    Dim Found As Range, FirstFound As String
    Set Found = Range("A:G").Find(-ActiveCell.Value, , xlValues, xlWhole)
    If IsNumeric(ActiveCell.Value) Then
        If Not Found Is Nothing Then
            FirstFound = Found.Address
            Do
                Found.Interior.ColorIndex = 3   'Red
                Set Found = Range("A:G").FindNext(Found)
            Loop Until Found.Address = FirstFound
        Else
            MsgBox "No signed-complement match found for " & ActiveCell.Value
        End If
    End If
End Sub
 
Sub Highlight_Clear()
    Range("A:G").Interior.ColorIndex = xlNone
End Sub

thank u very much,works perfect.

if i want to filter and see only the rows which have been painted in red,what should i do? (what code should i add).

cause i have like 3000 rows and i need scroll and i find the red cells and its very confusing...


i remind u again,its a pivot table

 

[AlphaFrog]

If you had a column in the pivot table that had the absolute value, then couldn't you just pivot on that?