Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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 "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: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
"phmckeever" wrote: 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 "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: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |