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:
1) If the data in Column A2 equal "ign" and Column A3 equals "leg" and
2) If columns B2 and B3 record numbers are the same then
3) Find the cells within range c2 and ae3 that are different and highlight
them.

This will be comparing two rows of cells, c2 thru ae2 and c3 thru ae3.

I have the following 'conditional formatting' formula as part of a subroutine.
Record1 = Range("$b2")
Record2 = Range(€œ$b3")
Do While Record1 < ""
If Record1 = Record2 Then
Range("$C2:$ae3").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A1=""ign"",$A2=""leg"",$B2=$B3,C2<AE3) "
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
End With
Selection.FormatConditions(1).Interior.ColorIndex = 40
End If
Loop

This routine identifies the records and selects the rows. But, it does not
highlight the difference cells nor does it move to the next rows of data.

Can you tell me what I am doing wrong?

phmckeever