Hightlight Row Range if Criteria is Satisfied

[Gos-C]

Hi all,

I need the following code to highlight A:EF if the row has the value 4000 or 4500 in column J. It does that.

The function checks if a corresponding record, which indicate void, is found - F, G and CE will be the same as the original record. If one is found, I want the code to highlight A:EF of the original record.

I need some help to fix the red portion of the code, please. I can't figure out how to code it.

Sub Highlight_Exclusions()
    Dim oLastRow As Long
    Dim c As Range, hRng As Range, n As Long
'    Const CC As Integer = 3 * 26 + 3     'column 'CC' as a number
    Const A As Integer = 1     'column 'G' as a number
'    Const EC As Integer = 5 * 26 + 3     'column 'EC' as a number
    Const EF As Integer = 5 * 26 + 6     'column 'EF' as a number
    Const J As Integer = 10     'column 'J' as a number

    Application.ScreenUpdating = False
    
    oLastRow = Cells.SpecialCells(xlLastCell).Row  'report original lastrow
    
    For n = 2 To oLastRow
        Set c = ActiveSheet.Cells(n, J)    'set range to row n, column J
        Set hRng = ActiveSheet.Range(Cells(n, A), Cells(n, EF))   'set range to row n, column G
        If c.Value = 4000 Or c.Value = 4500 Then     '4000 & 4500 claim
    hRng.Interior.ColorIndex = 36
    
   [COLOR="#FF0000"][B]For Each hRng In ActiveSheet
   If CheckF_G_CE(Range("F" & Target.Row), Range("G" & Target.Row), _
               Range("CE" & Target.Row), Target.Row) Then
    hRng.Interior.ColorIndex = 36
    End If
    Next n
    
   End If
   Next hRng
[/B][/COLOR]   
   Application.ScreenUpdating = True
End Sub

Public Function CheckF_G_CE(Fstr As String, Gstr As String, _
                         CEstr As String, Rw As Long) As Boolean
    Dim vFIND As Range

    On Error Resume Next
    Set vFIND = Range("F:F").Find(Fstr, After:=Range("F" & Rw), LookIn:=xlValues, LookAt:=xlWhole)
        If Not vFIND Is Nothing Then
            Do Until vFIND.Row = Rw
                If Range("G" & vFIND.Row) = Gstr And _
                   Range("CE" & vFIND.Row) = CEstr Then ' And _
                    CheckF_G_CE = True
                    Exit Function
                End If
                Set vFIND = Range("F:F").FindNext(vFIND)
            Loop
        End If
   ' by default if we reach this point, the result is FALSE, no duplicates found
    End Function

Thank you,
Gos-C

 

[farmerscott]

The function checks if a corresponding record, which indicate void, is found - F, G and CE will be the same as the original record. If one is found, I want the code to highlight A:EF of the original record.

Gos-C,

Can you explain the line above a little more. Can you give an example of a before and after table.

thx

FarmerScott

 

[Gos-C]

Hi Farmerscott,

Thank you very much for responding to my request for help - sorry for the delay in replying. Hope you are able to understand the following.

A J F G CE EE EF
1 . . . TXN CODE STORE ID TXN NO CONFIRM NO TXN TYPE . . .
2 . . . 3000 65623076 755136 752101 PAID . . .
3 . . . 3000 65623076 755187 753510 PAID . . .
4 . . . 4000 65623076 755187 753510 PDVOID . . .
5 . . . 4500 65623076 756149 753887 DPDVOID . . .

Rows 4 and 5 will be highlighted because column J has the codes 4000 and 4500. My macro does that correctly.

I borrowed the function from another macro but that was a Private Sub Worksheet_SelectionChange(ByVal Target As Range) so it worked when the user clicked the cell – see the following link:

http://en.allexperts.com/q/Excel-1059/2011/8/vba-code-mark-eligible

In this case, I want:

If column EE is “PAID” and there is another record where columns F, G and CE match the PAID record, the match will be a void of that “PAID” record. So, for the PAID record in row 3, columns F, G and CE of row 4 match those of row 3, therefore, row 3 should be highlighted (because it was voided).

Hope that paints a better picture.

Gos-C

 

[farmerscott]

Cos-C,

I am still trying to get my head around how to do this. Sorry but I am no VBA expert.

So the logic I need the code to do is....

1. find all the rows that have 4000 or 4500 in Col J.
2. then filter on all the possible numbers in Col F (in your example 65623076)
3. then do another filter on all the possible numbers in Col G (in your example 755187 and 756149)
4. repeating this again in Col CE.
5. then finally have 'paid' in Col EE.
6. highlight matching row from Col A to Col EF.

Do I have it right?

FarmerScott

 

[Gos-C]

Hi farmerscott,

I have #1 working with this snippet:

For n = 2 To oLastRow
        Set c = ActiveSheet.Cells(n, J)    'set range to row n, column J
        Set hRng = ActiveSheet.Range(Cells(n, A), Cells(n, EF))   'set range to row n, column G
        If c.Value = 4000 Or c.Value = 4500 Then     '4000 & 4500 claim
    hRng.Interior.ColorIndex = 36

I don't think filtering would work for what I am trying to accomplish. For example, if you filter Paid, everything would be unique - no match would be found. A duplicate is found when a Paid is voided, creating two identical rows except for column EF. For one, column EF will have Paid while the other will have PDVOID. It's the one that has Paid that I want highlighted.

HTH

Gos-C

 

[starl]

For starters, your code won't work because you are using "Target" as a variable, but it hasn't been declared. I'm guessing you picked code from a Change event. That variable is an argument in a change event, so Excel knows what it is. But in the original code you posted, it's not. Try compiling that code - you'll get an error right away.

Either put the code in a change event or tell Excel what "Target" is.

 

[Gos-C]

Thank you very much for pointing out the error, starl. I will try to fix it.

Greatly appreciated,
Gos-C

 

[farmerscott]

Gos-C,

just a quick question, what are you going to do once the 'matching' is done? I could not get a sense of what you wanted to do after this, as it will effect the code we write.

So if you had the data as follows

A
B
C
A
D
B
E

the two A's and B's match so they would be highlighed as follows.


A
B
C
A
D
B
E

I am thinking if there is a lot of 'matches' then it would be a little confusing. Would it be any easier is we copied or cut the 'matches' to another sheet so sheet 2 looked like

A
A
B
B

or do you want to find each 'match' and deal with them one at a time, while they are in your data?

Is there any data in Col EG?

thanks

FarmerScott

 

[Gos-C]

Hi farmerscott,

I will be using the interior color on the matching items as a means of identifying the items that are not allowed to be selected by the user – i.e, prevent the user from selecting those ones.

The first condition for selecting an item is that column EE must have “Paid.” One of the other conditions is that the Paid item must not have been subsequently voided. If a Paid item was subsequently voided, there would be an additional record with columns F, G and CE matching the “Paid” item but column EE would have “PDVoid.” Hence, the reason for highlighting the “Paid” item that matched the “PDVoid” item.

I am working on a plan B in which I concatenate F, G and CE temporarily in column EG, find and highlight the matched items and them delete column EG. It appears to be workable.

What do you thing about that?

 

[farmerscott]

Cos-C,

sorry it has taken a few days, but try-

Sub Highlight_Exclusions()
Dim oLastRow As Long
Dim c As Range, hRng As Range, n As Long, cel As Range
Dim text As String
Dim x As Long
Dim d As Range



' Const CC As Integer = 3 * 26 + 3 'column 'CC' as a number
Const A As Integer = 1 'column 'G' as a number
' Const EC As Integer = 5 * 26 + 3 'column 'EC' as a number
Const EF As Integer = 5 * 26 + 6 'column 'EF' as a number
Const J As Integer = 10 'column 'J' as a number
Application.ScreenUpdating = False

oLastRow = Cells.SpecialCells(xlLastCell).Row 'report original lastrow


Range("EG2:EG" & oLastRow).FormulaR1C1 = "=concatenate(RC[-130],RC[-131],RC[-54])"


For n = 2 To oLastRow
Set c = ActiveSheet.Cells(n, J) 'set range to row n, column J
Set hRng = ActiveSheet.Range(Cells(n, A), Cells(n, EF)) 'set range to row n, column G
If c.Value = 4000 Or c.Value = 4500 Then '4000 & 4500 claim
hRng.Interior.ColorIndex = 36
text = c.Offset(0, 127).Value
For x = 2 To oLastRow
Set d = ActiveSheet.Cells(x, 137)
Set hRng = ActiveSheet.Range(Cells(x, A), Cells(x, EF))
If d.Value = text And d.Offset(, -2).Value = "Paid" Then
hRng.Interior.Color = vbRed
End If
Next x

End If

Next n

Range("EG2:EG" & oLastRow).ClearContents



Application.ScreenUpdating = True
End Sub

Matches are highlighed in red.

Hope that helps,

FarmerScott