Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Cell Formatting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Cell Formatting
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Cell Formatting
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

--
Regards,
Tom Ogilvy

"phmckeever" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Cell Formatting
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"phmckeever" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Cell Formatting
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
"phmckeever" wrote:

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"phmckeever" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Cell Formatting
Sounds like you need to declare s as String.

Here's how

Dim s as String

placed before s is used in the module.



--
Regards,
Tom Ogilvy


"phmckeever" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"phmckeever" wrote:

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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Cell Formatting
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


"Tom Ogilvy" wrote:

Sounds like you need to declare s as String.

Here's how

Dim s as String

placed before s is used in the module.



--
Regards,
Tom Ogilvy


"phmckeever" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"phmckeever" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting--different formatting depending on cell con Tammy S. Excel Discussion (Misc queries) 3 March 30th 09 08:11 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 0 January 15th 07 04:35 PM
expanding custom formatting without removing existing cell formatting? Keith Excel Worksheet Functions 3 December 27th 06 01:54 PM


All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement