> Hi Bill,
>
> It's me again. One last condition needs to be address in the code below.
>
> As you know, the code finds occurance of 'ign' and 'leg' in column A; it
> then checks that the ID Numbers in column B are identical. Once these are
> done, it finds the cells in the 'ign' and 'leg' rows that are different and
> highlights the cells.
>
> The last piece is that I need for the code to 'skip' any row that does not
> have a 'ign'/'leg' match. In other words, if it is only 'ign' or only leg,
> SKIP THAT ROW and begin the matching with the very next row.
>
> As always, your assistance is greatly appreciated.
>
> phmckeever
>
> "Bill Pfister" wrote:
>
> > Happy to help.
> >
> > Bill
> >
> >
> > "phmckeever" wrote:
> >
> > > Bill,
> > >
> > > Your code worked like a charm; I am so pleased and so is my boss. Thank you
> > > so much for all your help. Also, I made a mistake. There is a later version
> > > of the question, please disregard it; or, close it, if you can.
> > >
> > > Again, thank you so much.
> > >
> > > phmckeever
> > >
> > > "Bill Pfister" wrote:
> > >
> > > > When you used the "2" and "3", you were shifting too far. I've made the
> > > > change (and reformatted a little). Try this:
> > > >
> > > >
> > > > Public Sub TestFormat()
> > > >
> > > > Dim Record1 As Range
> > > > Dim Record2 As Range
> > > > Dim rngAll As Range
> > > > Dim strFormula As String
> > > > Dim strRow1 As String
> > > > Dim strRow2 As String
> > > > Dim x As Long
> > > > Dim lngRow As Long
> > > >
> > > > Set Record1 = Range("$b2")
> > > > Set Record2 = Range("$b3")
> > > > Range("A1").Select
> > > >
> > > > Do While (Record1 <> "")
> > > > If (Record1 = Record2) Then
> > > > lngRow = Record1.Row
> > > >
> > > > strRow1 = Trim$(Str$(lngRow))
> > > > strRow2 = Trim$(Str$(lngRow + 1))
> > > >
> > > > Set rngAll = Range("$C" & strRow1 & ":$AE" & strRow2)
> > > > rngAll.FormatConditions.Delete
> > > >
> > > > For x = 1 To rngAll.Columns.Count
> > > >
> > > > strFormula = "=AND($A" & strRow1 & "=""ign""," & _
> > > > "$A" & strRow2 & "=""leg""," & _
> > > > "$B" & strRow1 & "=$B" & strRow2 & "," & _
> > > > rngAll.Cells(1, x).Address & "<>" & _
> > > > rngAll.Cells(2, x).Address & ")"
> > > >
> > > > rngAll.Cells(1, x).Select
> > > > Selection.FormatConditions.Add _
> > > > Type:=xlExpression, Formula1:=strFormula
> > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > >
> > > > rngAll.Cells(2, x).Select
> > > > Selection.FormatConditions.Add _
> > > > Type:=xlExpression, Formula1:=strFormula
> > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > >
> > > > Next x
> > > > End If
> > > >
> > > > Set Record1 = Record1.Offset(2, 0)
> > > > Set Record2 = Record2.Offset(2, 0)
> > > > Loop
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "phmckeever" wrote:
> > > >
> > > > > PROCEDURE:
> > > > > Dim Record1 As Range
> > > > > Dim Record2 As Range
> > > > > Dim rngAll As Range
> > > > > Dim strFormula As String
> > > > > Dim strRow1 As String
> > > > > Dim strRow2 As String
> > > > > Dim x As Long
> > > > > Dim IngRow As Long
> > > > >
> > > > > Set Record1 = Range("$b2")
> > > > > Set Record2 = Range("$b3")
> > > > > Range("A1").Select
> > > > > Do While (Record1 <> "")
> > > > > If (Record1 = Record2) Then
> > > > > IngRow = Record1.Row
> > > > >
> > > > > strRow1 = Trim$(Str$(IngRow))
> > > > > strRow2 = Trim$(Str$(IngRow + 1))
> > > > >
> > > > > Set rngAll = Range("$C" & strRow1 & ":$AE" & strRow2)
> > > > > rngAll.FormatConditions.Delete
> > > > >
> > > > > For x = 1 To rngAll.Columns.Count
> > > > >
> > > > > strFormula = "=AND($A" & strRow1 & "=""ign"",$A" & strRow2 & _
> > > > > "=""leg"",$B" & strRow1 & "=$B" & strRow2 & "," & _
> > > > > rngAll.Cells(2, x).Address & _
> > > > > "<>" & rngAll.Cells(3, x).Address & ")"
> > > > >
> > > > > rngAll.Cells(2, x).Select
> > > > > Selection.FormatConditions.Add Type:=xlExpression, _
> > > > > Formula1:=strFormula
> > > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > > >
> > > > > rngAll.Cells(3, x).Select
> > > > > Selection.FormatConditions.Add Type:=xlExpression, _
> > > > > Formula1:=strFormula
> > > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > > >
> > > > > Next x
> > > > > End If
> > > > >
> > > > > Set Record1 = Record1.Offset(2, 0)
> > > > > Set Record2 = Record2.Offset(2, 0)
> > > > > Loop
> > > > >
> > > > > RESULTS
> > > > > A B C D E
> > > > > 1 ign 747002 1-Jan-80 1-Jan-49 ADOPTION AGENCIES
> > > > > 2 leg 747002 1-Jan-80 1-Jan-10 ADOPTION AGENCIES
> > > > >
> > > > > 3 ign 778966 31-Dec-79 1-Jan-10 CLEANING SERVICE
> > > > > 4 leg 778966 1-Jan-80 1-Jan-49 CLEANING SERVICE
> > > > >
> > > > > 5 ign 800164 1-Jan-80 31-Dec-79 HOT TUBS & SPAS-DEALERS
> > > > > 6 leg 800164 1-Jan-80 1-Jan-49 HOT TUBS & SPAS-DEALERS
> > > > >
> > > > > Lines 1 and 2, highlighted c1 and c2 but not d1 and d2, which is the
> > > > > different data. Also highlighted e1 and e2, which it should not have.
> > > > >
> > > > > Lines 3 and 4 highlighted d3 and d4,which is right; but also highlighted e3
> > > > > and e4, which is not right.
> > > > >
> > > > > Lines 5 and 6, highlighted c5 and c6, which it should not have; highlighted
> > > > > e5 and e6, which it should not have, and did not highlight d5 and d6, which
> > > > > it should have.
> > > > >
> > > > > Also, the cells highlighting does not begin until row 47. It skipped rows 1
> > > > > – 46.
> > > > >
> > > > > And, you will notice that I changed a couple of reference from 1 and 2 to 2
> > > > > and 3. There is a header row on the worksheet. Is that a problem?
> > > > >
> > > > > Thank you in advance.
> > > > >
> > > > > phmckeever
> > > > >
> > > > > "Bill Pfister" wrote:
> > > > >
> > > > > > Give this a shot:
> > > > > >
> > > > > > Public Sub FormatAlgorithm()
> > > > > > Dim Record1 As Range
> > > > > > Dim Record2 As Range
> > > > > > Dim rngAll As Range
> > > > > > Dim strFormula As String
> > > > > > Dim strRow1 As String
> > > > > > Dim strRow2 As String
> > > > > > Dim x As Long
> > > > > > Dim lngRow As Long
> > > > > >
> > > > > > Set Record1 = Range("$b1")
> > > > > > Set Record2 = Range("$b2")
> > > > > >
> > > > > > Do While (Record1 <> "")
> > > > > > If (Record1 = Record2) Then
> > > > > > lngRow = Record1.Row
> > > > > >
> > > > > > strRow1 = Trim$(Str$(lngRow))
> > > > > > strRow2 = Trim$(Str$(lngRow + 1))
> > > > > >
> > > > > > Set rngAll = Range("$C" & strRow1 & ":$AE" & strRow2)
> > > > > > rngAll.FormatConditions.Delete
> > > > > >
> > > > > > For x = 1 To rngAll.Columns.Count
> > > > > >
> > > > > > strFormula = "=AND($A" & strRow1 & "=""ign"",$A" & strRow2 & _
> > > > > > "=""leg"",$B" & strRow1 & "=$B" & strRow2 & "," & _
> > > > > > rngAll.Cells(1, x).Address & _
> > > > > > "<>" & rngAll.Cells(2, x).Address & ")"
> > > > > >
> > > > > > rngAll.Cells(1, x).Select
> > > > > > Selection.FormatConditions.Add Type:=xlExpression,
> > > > > > Formula1:=strFormula
> > > > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > > > >
> > > > > > rngAll.Cells(2, x).Select
> > > > > > Selection.FormatConditions.Add Type:=xlExpression,
> > > > > > Formula1:=strFormula
> > > > > > Selection.FormatConditions(1).Interior.ColorIndex = 6
> > > > > >
> > > > > > Next x
> > > > > >
> > > > > > End If
> > > > > >
> > > > > > Set Record1 = Record1.Offset(2, 0)
> > > > > > Set Record2 = Record2.Offset(2, 0)
> > > > > >
> > > > > > Loop
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bill Pfister" wrote:
> > > > > >
> > > > > > > This won't be difficult, but I have a quick question: is the intent to have
> > > > > > > the algorithm select all the row pairs (1&2, 3&4, 5&6) & does it keep going
> > > > > > > down until there are no more rows?
> > > > > > >
> > > > > > >
> > > > > > > "phmckeever" wrote:
> > > > > > >
> > > > > > > > SAMPLE OF DATA
> > > > > > > > A B C D E
> > > > > > > > sys Num Begin End NAME
> > > > > > > > 1 ign 1613 1-Jan-80 1-Jan-10 BILLIARD SUPPLIES
> > > > > > > > 2 leg 1613 1-Jan-80 1-Jan-49 BILLIARD EQUIP & SUPPLIES
> > > > > > > >
> > > > > > > > 3 ign 4247 1-Jan-80 1-Jan-10 CREDIT & DEBT SERVICE
> > > > > > > > 4 leg 4247 1-Jan-80 1-Jan-49 CREDIT & DEBT SERVICE
> > > > > > > >
> > > > > > > > 5 ign 9080 31-Dec-79 1-Jan-10 HOT TUBS & SPAS-DEALERS
> > > > > > > > 6 leg 9080 1-Jan-80 1-Jan-10 HOT TUBS & SPAS-DEALERS
> > > > > > > >
> > > > > > > >
> > > > > > > > This is only the 5 columns of the data; the columns are from a – ae
> > > > > > > >
> > > > > > > > The code should select rows 1 and 2 and highlight data in cells d1 and d2
> > > > > > > > and e1 and e2, because they differ.
> > > > > > > >
> > > > > > > > In rows 3 and 4, d3 and d4 cells should be highlighted, and
> > > > > > > >
> > > > > > > > In rows 5 and 6, c5 and c6 should be highlighted.
> > > > > > > >
> > > > > > > > I hope this makes it clearer. If not, please let me know. I am coming down
> > > > > > > > to the wire on this. I have to give a report tomorrow at 1 p.m.
> > > > > > > >
> > > > > > > > phmckeever
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bill Pfister" wrote:
> > > > > > > >
> > > > > > > > > Your conditional formatting formula does not correspond to your description.
> > > > > > > > >
> > > > > > > > > First: "=AND($A1=""ign"",$A2=""leg"",$B2=$B3,C2<>AE3)"
> > > > > > > > > then: "If the data in Column A2 equal "ign" and Column A3 equals "leg""
> > > > > > > > >
> > > > > > > > > If you still can't resolve the problem beyond this, would you mind providing
> > > > > > > > > an actual example of the data? i.e:
> > > > > > > > >
> > > > > > > > > ign 123 456 789 012
> > > > > > > > > leg 123 456 779 012
> > > > > > > > > ^--------- highlight this cell
> > > > > > > > >
> > > > > > > > > Also, are you certain that the "ign" and "leg" rows are paired/lined up
> > > > > > > > > after sorting ?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "phmckeever" wrote:
> > > > > > > > >
> > > > > > > > > > Rows of data from two sources have been combined and sorted on one worksheet.
> > > > > > > > > > To identify the source of the data, the rows are 'flagged' with the acromyns
> > > > > > > > > > "ign" or "leg". The only reason they are appearing on this report is that
> > > > > > > > > > there is/are cell(s) of data that have been incorrectly entered in one of the
> > > > > > > > > > two records.
> > > > > > > > > >
> > > > > > > > > > In this worksheet, I need to find and highlight the following:
Similar Threads
