Conditional Formatting

[phmckeever]

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

 

[Bill Pfister]

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]

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]

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?

 

[Bill Pfister]

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

 

[phmckeever]

Bill,

The answer is yes to both questions. It should select all the pair rows;
and, it should search the entire worksheet for them.

Just got in; I will try your algorithm now. I will keep let you know what
happens. You are a great person to do this for me. Have a wonderful day

phmckeever

 

[phmckeever]

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]

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]

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]

Happy to help.

Bill

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

 

[phmckeever]

Hi Bill,

It 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

 

[phmckeever]

Hi Bill,

It 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