Cell Formatting

[phmckeever]

As you see from the code, it finds occurrences of ‘ign’ and ‘leg’ in column
A, considered a match; it, then, checks that the ID numbers in column B are
identical.

Once these two conditions are met, it finds the cells in the ‘ign’ and ‘leg’
rows, ie c2 – ae3, and highlights the cells in the range that are different.

The last piece I need is to have the code skip the ‘ign’/’leg’ rows that do
not have a match, only one ‘ign’ or only one ‘leg’.

HERE IS THE CODE I HAVE:
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))

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

This code works fine; I just need it to skip the unmatched rows. Thanks!
phmckeever

 

[Tom Ogilvy]

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 & ":$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

End If

Set Record1 = Record1.Offset(2, 0)
Set Record2 = Record2.Offset(2, 0)
Loop

 

[phmckeever]

Tom,

When I run your code, I get a variable not defined error. It stops on this
row:

s = "=AND($A" & strRow1 & "=""ign"", " & _

With the 's=' highlighted.

Please help.

phmckeever

 

[phmckeever]

Hi Tim,

Also, here is a 'cut and paste' from the worksheet of the columns that are
affected:

1 ign ABGN VA
2 leg ABGN *
3 ign ABVL KS
4 leg ABVL *
5 leg ADA *
6 ign ADAM OH
7 leg ADAM *
8 ign ADAR OH
9 leg ADAR *
10 ign AHSK NC
11 leg AHSK *
12 ign ALDN KS
13 leg ALDN *

The code is highlighting cells in the last column above for rows 1, 2, 3 and
4, which it should because these cells are different.

And it does not highlight cell 5 in the last column, which it should not
because there is no match.

However, it is not picking up at cells 6 and 7, 8 and 9, 10 and 11, and 12
and 13, which it should because they fit the criteria for a match, ign and
leg and ID code are the same.

And, I took care to the variable error by declaring 's' as a string.

Thank you in advance.

phmckeever

 

[Tom Ogilvy]

Sounds like you need to declare s as String.

Here's how

Dim s as String

placed before s is used in the module.

 

[phmckeever]

Hi Tim,

Here is a 'cut and paste' from the worksheet of the columns that are
affected:

1 ign ABGN VA
2 leg ABGN *
3 ign ABVL KS
4 leg ABVL *
5 leg ADA *
6 ign ADAM OH
7 leg ADAM *
8 ign ADAR OH
9 leg ADAR *
10 ign AHSK NC
11 leg AHSK *
12 ign ALDN KS
13 leg ALDN *

The code is highlighting cells in the last column above for rows 1, 2, 3 and
4, which it should because these cells are different.

And it does not highlight cell 5 in the last column, which it should not
because there is no match.

However, it is not picking up at cells 6 and 7, 8 and 9, 10 and 11, and 12
and 13, which it should because they fit the criteria for a match, ign and
leg and ID code are the same.

Thank you in advance.

phmckeever