About Us

phmckeever

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:

PROCEDU
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:

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SUM of conditional formatting cells	Esradekan	Excel Worksheet Functions	3	October 10th 08 10:17 AM
Conditional Formatting and sum of cells	Amanda	Excel Discussion (Misc queries)	1	June 10th 08 09:07 PM
Formatting cells in a column with conditional formatting?	shamor	Excel Discussion (Misc queries)	8	May 19th 08 10:11 PM
conditional formatting 2 cells	debjocc	Excel Worksheet Functions	6	June 2nd 06 03:21 PM
Conditional Formatting Multiple cells based on 2 cells	Louis Markowski	Excel Worksheet Functions	2	June 1st 05 05:26 PM

Menu

About Us