How to highlight the positive and negative numbers, if the amount is split

[Feroz90]

Hi All,

If the positive amount is 100 and the negative amount have -75 and -25, will it highlight all these three.

Can anyone please help me with a VBA code to get these.

Thanks

 

[Feroz90]

Hi All,

Can anyone please give a try, I use the below code for highlighting positive and negative, however, I want to highlight if the positive amount is split into two different numbers.

Sub Knock_Off_Click()

Dim StartRow As Long, EndRow As Long
Dim rngCell As Range, rngMyData As Range
Dim MyCount As Long

StartRow = 3 'Starting row number for the data. Change to suit.
EndRow = Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set rngMyData = Range("D" & StartRow & ":D" & EndRow)

Application.ScreenUpdating = False

For Each rngCell In rngMyData
If Len(rngCell) > 0 And rngCell.Interior.Color = 16777215 Then
If MyCount > 10 Then
MyCount = 1
Else
MyCount = MyCount + 1
End If
Call HighlightOppositeSign(rngCell.Address, rngMyData.Address, MyCount)
End If
Next rngCell

Set rngMyData = Nothing

MyCount = 0

Application.ScreenUpdating = True

End Sub
Sub HighlightOppositeSign(strCellAddress As String, strDataRange As String, MyCount As Long)


Dim rngCell As Range, rngMyData As Range
Dim MyAmt As Double

MyAmt = CDbl(Range(strCellAddress))

For Each rngCell In Range(strDataRange)
If rngCell.Address <> strCellAddress Then
If rngCell.Value = MyAmt * -1 Then
If rngCell.Interior.Color = 16777215 Then
Select Case MyCount
Case Is = 1
Range(strCellAddress).Interior.Color = RGB(255, 255, 153)
rngCell.Interior.Color = RGB(255, 255, 153)
Exit For
End Select
End If
End If
End If
Next rngCell


End Sub

 

[Akuini]

Could you post some sample data, showing the result as expected?

 

[Feroz90]

<tbody>
[TD="align: center"] 100 [/TD]

[TD="align: center"] -25 [/TD]

[TD="align: center"] -75 [/TD]

[TD="align: center"] -180 [/TD]

[TD="align: center"] 100 [/TD]

[TD="align: center"] 80 [/TD]

</tbody>

It should be like this, if we add -25 & -75 we get 100, so it highlighted in Red and if we add 100 and 80, we get -180 which is highlighted in Blue.

All I want is if the split amount calculates and gets a amount in the column, it should highlight in yellow.

 

[Akuini]

It would be better if you can post some actual data, so I can understand "the patern" better.
For example:
In your example the 3 numbers (that match the criteria) are next to each other. Is that always the case or could your data be like this:
100
-180
100
-25
-75
80

So for each number, it's possible that we need to search all the way down to find 2 numbers that match the criteria.
How big is your data? thousands of rows?

All I want is if the split amount calculates and gets a amount in the column, it should highlight in yellow.

So all numbers that match the criteria should be highlighted yellow?
That won't confuse you? I mean, using the example above all numbers will be yellow, so you can't easily specify which 3 numbers that match the criteria.

 

[Feroz90]

Hi,

Thank you for your time, this is what I want, i have thousands of transactions like this, It is ok if it is totally highlighted in yellow.

Actual thing is, we should get the below amount reconcile, if you see in below example, if all the positive and negative amounts will come to zero.

And the amount 120 & 80 which does not have negative values remains still. I just want to nullify the positive and negative values.

If we try it in excel, if you just drop down the below six, you will get the total as "0", so i will take only those which are not reconciled.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Fruits[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]-70[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]-180[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Apple[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

It still isn't clear to me, you didn't answer this:

In your example the 3 numbers (that match the criteria) are next to each other. Is that always the case or could your data be like this:
100
-180
100
-25
-75
80

So for each number, it's possible that we need to search all the way down to find 2 numbers that match the criteria?
So for 100 (in first row), it will find the match i.e -25 & -75 in row 4 & 5, is it possible?

 

[Feroz90]

Yes that is fine, if the lets assume 100 is in A2, -25 in A15 & -75 in A20, it needs to gets highlighted in yellow

 

[Akuini]

Yes that is fine, if the lets assume 100 is in A2, -25 in A15 & -75 in A20, it needs to gets highlighted in yellow

Hm, in that case I hope somebody else could help you with this, because I don't know how to solve this issue.

 

[Feroz90]

Can anyone else give try.