| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Highlighting
Hello, I need the data in column C to be highlighted when rows begin with 'ign' and 'leg' in column A and have identical ID codes in column B. Here is a 'cut and paste' from the worksheet (The XXX is just placeholders to demonstrate; and the spacing between the rows is for clarity). A B C D E 1 ign ABGN XXX XXX VA 2 leg ABGN XXX XXX * 3 ign ABVL XXX XXX KS 4 leg ABVL XXX XXX * 5 leg ADA XXX XXX * 6 ign ADAM XXX XXX OH 7 leg ADAM XXX XXX * 8 ign ADAR XXX XXX OH 9 leg ADAR XXX XXX * 10 ign AHSK XXX XXX NC 11 leg AHSK XXX XXX * 12 ign ALDN XXX XXX KS 13 leg ALDN XXX XXX * The code below is highlighting cells e1 and e2, e3 and e4, which is correct because columns A and B meet the criteria. And cell e5 is not highlighted, which is also correct because it does not meet the criteria. However, the highlight is not being picked up at cells e6 and e7, e8 and e9, e10 and e11, and e12 and e13, which it should because Column A and B fit the criteria for these matches, ign and leg and identical id code. Can you please, pleasssssse help me with this code. Thank you in advance. phmckeever Range("A1").Select 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)) s = "=AND($A" & strRow1 & "=""ign"", " & _ "$A" & strRow2 & "=""leg""," & _ "$B" & strRow1 & "=$B" & strRow2 & ")" if evaluate(s) = true then Set rngAll = Range("$C" & strRow1 & ":$E" & 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 End If Set Record1 = Record1.Offset(2, 0) Set Record2 = Record2.Offset(2, 0) Loop |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Highlighting
"phmckeever" wrote: Hello, I need the data in column E to be highlighted when rows begin with 'ign' and 'leg' in column A and have identical ID codes in column B. Here is a 'cut and paste' from the worksheet (The XXX is just placeholders to demonstrate; and the spacing between the rows is for clarity). A B C D E 1 ign ABGN XXX XXX VA 2 leg ABGN XXX XXX * 3 ign ABVL XXX XXX KS 4 leg ABVL XXX XXX * 5 leg ADA XXX XXX * 6 ign ADAM XXX XXX OH 7 leg ADAM XXX XXX * 8 ign ADAR XXX XXX OH 9 leg ADAR XXX XXX * 10 ign AHSK XXX XXX NC 11 leg AHSK XXX XXX * 12 ign ALDN XXX XXX KS 13 leg ALDN XXX XXX * The code below is highlighting cells e1 and e2, e3 and e4, which is correct because columns A and B meet the criteria. And cell e5 is not highlighted, which is also correct because it does not meet the criteria. However, the highlight is not being picked up at cells e6 and e7, e8 and e9, e10 and e11, and e12 and e13, which it should because Column A and B fit the criteria for these matches, ign and leg and identical id code. Can you please, pleasssssse help me with this code. Thank you in advance. phmckeever Range("A1").Select 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)) s = "=AND($A" & strRow1 & "=""ign"", " & _ "$A" & strRow2 & "=""leg""," & _ "$B" & strRow1 & "=$B" & strRow2 & ")" if evaluate(s) = true then Set rngAll = Range("$C" & strRow1 & ":$E" & 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 End If Set Record1 = Record1.Offset(2, 0) Set Record2 = Record2.Offset(2, 0) Loop |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Highlighting
The first sentence after hello should read,
I need the data in column "E", not C. "phmckeever" wrote: Hello, I need the data in column C to be highlighted when rows begin with 'ign' and 'leg' in column A and have identical ID codes in column B. Here is a 'cut and paste' from the worksheet (The XXX is just placeholders to demonstrate; and the spacing between the rows is for clarity). A B C D E 1 ign ABGN XXX XXX VA 2 leg ABGN XXX XXX * 3 ign ABVL XXX XXX KS 4 leg ABVL XXX XXX * 5 leg ADA XXX XXX * 6 ign ADAM XXX XXX OH 7 leg ADAM XXX XXX * 8 ign ADAR XXX XXX OH 9 leg ADAR XXX XXX * 10 ign AHSK XXX XXX NC 11 leg AHSK XXX XXX * 12 ign ALDN XXX XXX KS 13 leg ALDN XXX XXX * The code below is highlighting cells e1 and e2, e3 and e4, which is correct because columns A and B meet the criteria. And cell e5 is not highlighted, which is also correct because it does not meet the criteria. However, the highlight is not being picked up at cells e6 and e7, e8 and e9, e10 and e11, and e12 and e13, which it should because Column A and B fit the criteria for these matches, ign and leg and identical id code. Can you please, pleasssssse help me with this code. Thank you in advance. phmckeever Range("A1").Select 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)) s = "=AND($A" & strRow1 & "=""ign"", " & _ "$A" & strRow2 & "=""leg""," & _ "$B" & strRow1 & "=$B" & strRow2 & ")" if evaluate(s) = true then Set rngAll = Range("$C" & strRow1 & ":$E" & 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 End If Set Record1 = Record1.Offset(2, 0) Set Record2 = Record2.Offset(2, 0) Loop |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Highlighting
Regarding your requirement ...and have identical ID codes in column
B... Do you mean Rows 1 and 2, 3 and 4, etc. have to match in column B to complete the highlighting? phmckeever wrote: The first sentence after hello should read, I need the data in column "E", not C. "phmckeever" wrote: Hello, I need the data in column C to be highlighted when rows begin with 'ign' and 'leg' in column A and have identical ID codes in column B. Here is a 'cut and paste' from the worksheet (The XXX is just placeholders to demonstrate; and the spacing between the rows is for clarity). A B C D E 1 ign ABGN XXX XXX VA 2 leg ABGN XXX XXX * 3 ign ABVL XXX XXX KS 4 leg ABVL XXX XXX * 5 leg ADA XXX XXX * 6 ign ADAM XXX XXX OH 7 leg ADAM XXX XXX * 8 ign ADAR XXX XXX OH 9 leg ADAR XXX XXX * 10 ign AHSK XXX XXX NC 11 leg AHSK XXX XXX * 12 ign ALDN XXX XXX KS 13 leg ALDN XXX XXX * The code below is highlighting cells e1 and e2, e3 and e4, which is correct because columns A and B meet the criteria. And cell e5 is not highlighted, which is also correct because it does not meet the criteria. However, the highlight is not being picked up at cells e6 and e7, e8 and e9, e10 and e11, and e12 and e13, which it should because Column A and B fit the criteria for these matches, ign and leg and identical id code. Can you please, pleasssssse help me with this code. Thank you in advance. phmckeever Range("A1").Select 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)) s = "=AND($A" & strRow1 & "=""ign"", " & _ "$A" & strRow2 & "=""leg""," & _ "$B" & strRow1 & "=$B" & strRow2 & ")" if evaluate(s) = true then Set rngAll = Range("$C" & strRow1 & ":$E" & 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 End If Set Record1 = Record1.Offset(2, 0) Set Record2 = Record2.Offset(2, 0) Loop |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell changes highlighting | Excel Programming | |||
| Cell Highlighting | Excel Discussion (Misc queries) | |||
| Highlighting A Cell | Excel Discussion (Misc queries) | |||
| Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
| Highlighting a cell | Excel Programming | |||
Linear Mode
