Bob,
Thank you; your code works!!!!!!!!!!!!!
phmckeever
"Bob Phillips" wrote:
> Range("C2").Select
> Set rngAll = Range("$C" & strRow1 & ":$E" & strRow2)
> With rngAll
>
> .FormatConditions.Delete
>
> strFormula = "=AND($A2=""ign"",$A3=""leg"",$B2=$B3,C2<>C3)"
>
> .FormatConditions.Add _
> Type:=xlExpression, Formula1:=strFormula
> .FormatConditions(1).Interior.ColorIndex = 6
>
> strFormula = "=AND($A1=""ign"",$A2=""leg"",$B1=$B2,C1<>C2)"
> .FormatConditions.Add _
> Type:=xlExpression, Formula1:=strFormula
> .FormatConditions(2).Interior.ColorIndex = 6
>
> End With
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "phmckeever" <(E-Mail Removed)> wrote in message
> news:8EF9819F-9D44-41D3-8938-(E-Mail Removed)...
> > I need the data in column E to be highlighted when
> > rows begin with 'ign' and 'leg' in column A and have identical ID code
> > in column B.
> >
> > Here is a 'cut and paste' from the worksheet (The XXX is just placeholders
> > to demonstrate).
> >
> > 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 a
> > match, 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
> >
> >
>
>
>
|
Similar Threads
