Hi there,

I'm trying to use a loop to copy a conditional format from row to row. The
conditional format is to compare row A### to the row above and the row below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result is not
the same the colour should change. Here is the code I have used (it's very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help either.

The format is copying but only looking at the data in row 26.

Please help!!!

This puts in the conditional format your code appears to want to put in. If
I am in A26, I am not sure why I would check A27 and A28, so I am not sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help either.

The format is copying but only looking at the data in row 26.

Please help!!!

thanks....I tried what you suggested (copied it directly into my code) but it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066 =
$A64067 and the second part shows as ="$A27 = $A28". The D to F comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put in. If
I am in A26, I am not sure why I would check A27 and A28, so I am not sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help either.

The format is copying but only looking at the data in row 26.

Please help!!!

Try this one:

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26=$A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A27=$A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.Offset(0, 3)(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
thanks....I tried what you suggested (copied it directly into my code) but
it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066 =
$A64067 and the second part shows as ="$A27 = $A28". The D to F
comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put in.
If
I am in A26, I am not sure why I would check A27 and A28, so I am not
sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result
is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help
either.

The format is copying but only looking at the data in row 26.

Please help!!!

Thank You!! that worked....well sort of.

I made one change so that if $a26 = $a25
next $a26 = $a27
so that it checks the line above and the line below.

the only problem is it only changes the color of one of the duplicates. for
example:
a26 JOHN SMITH
a27 JOHN SMITH
a28 JOHN SMITH

in the example shown above, only A27 & A28 are the highlighted colour. Do
you know how I would get it to change A26 as well?
"Tom Ogilvy" wrote:

Try this one:

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26=$A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A27=$A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.Offset(0, 3)(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
thanks....I tried what you suggested (copied it directly into my code) but
it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066 =
$A64067 and the second part shows as ="$A27 = $A28". The D to F
comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put in.
If
I am in A26, I am not sure why I would check A27 and A28, so I am not
sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result
is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help
either.

The format is copying but only looking at the data in row 26.

Please help!!!

PLEASE DISREGARD MY LAST MESSAGE.

IT WORKS OKAY NOW.

THANKS SO MUCH FOR YOUR HELP!!!!!!

"Tom Ogilvy" wrote:

Try this one:

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26=$A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A27=$A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.Offset(0, 3)(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
thanks....I tried what you suggested (copied it directly into my code) but
it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066 =
$A64067 and the second part shows as ="$A27 = $A28". The D to F
comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put in.
If
I am in A26, I am not sure why I would check A27 and A28, so I am not
sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result
is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help
either.

The format is copying but only looking at the data in row 26.

Please help!!!

Hi again,

hopefully this is the last question...is there anyway to use the three
conditions together?
Like if D26< F26 and A26= A27 for equation 1 and
D26<F26 and A26=A25 for equation 2?


"Tom Ogilvy" wrote:

Try this one:

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26=$A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A27=$A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.Offset(0, 3)(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
thanks....I tried what you suggested (copied it directly into my code) but
it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066 =
$A64067 and the second part shows as ="$A27 = $A28". The D to F
comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put in.
If
I am in A26, I am not sure why I would check A27 and A28, so I am not
sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 = $A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 = $A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to row.
The
conditional format is to compare row A### to the row above and the row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the result
is
not
the same the colour should change. Here is the code I have used (it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help
either.

The format is copying but only looking at the data in row 26.

Please help!!!

Untested, but try this:

Sub CCC
Dim rng as Range
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($A26=$A27,$D26<$F26)"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($A26=$A25,$D26<$F26)"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($A26=$A27,$D26<$F26)"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($A26=$A25,$D26<$F26)"
.FormatConditions(2).Interior.ColorIndex = 35
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
Hi again,

hopefully this is the last question...is there anyway to use the three
conditions together?
Like if D26< F26 and A26= A27 for equation 1 and
D26<F26 and A26=A25 for equation 2?


"Tom Ogilvy" wrote:

Try this one:

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
rng(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=And($A26=$A27,$D26<$F26)"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression,
Formula1:="=AND($A26=$A25",$D26<$F26)"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.Offset(0, 3)(1).Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"lost!!" wrote in message
...
thanks....I tried what you suggested (copied it directly into my code)
but
it
didn't work.
I need to check a27 & a28 because I am looking for duplicate entries.
earlier in my code I have column A being sorted alphabetically.

For the col A comparison, the first part of the code shows as =$A64066
=
$A64067 and the second part shows as ="$A27 = $A28". The D to F
comparison
looks the same as the first A - 64066.

sorry if I'm being thick.....
"Tom Ogilvy" wrote:

This puts in the conditional format your code appears to want to put
in.
If
I am in A26, I am not sure why I would check A27 and A28, so I am
not
sure
that is the right formula, but you wrote it.

Sub BBB()
Set rng = Range(Range("A26"), Range("A26").End(xlDown))
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A26 =
$A27"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="$A27 =
$A28"
.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
With .Offset(0, 3).Resize(, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D26<$F26"
.FormatConditions(1).Interior.ColorIndex = 39
End With
End With
End Sub

--
Regards,
Tom Ogilvy


"lost!!" wrote in message
...
Hi there,

I'm trying to use a loop to copy a conditional format from row to
row.
The
conditional format is to compare row A### to the row above and the
row
below
- if a duplicate is found then the colour should change.
The next part is to compare the value in D## to F## and if the
result
is
not
the same the colour should change. Here is the code I have used
(it's
very
simple since I'm just new at programming):

' check to see if row is equal to row above or below
'
Range("A26").Select
While ActiveCell.Value < ""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A$26 =
$A$27"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="$A$27 =
$A$28"
Selection.FormatConditions(2).Interior.ColorIndex = 35
'
' check to see if rate charged was incorrect - compare d & f
'
ActiveCell.Offset(0, 3).Range("A1:C1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=
_
"=$D26<$F26"
Selection.FormatConditions(1).Interior.ColorIndex = 39
ActiveCell.Offset(1, -3).Range("A1").Select
Wend

I have tried removing the $ from the format but that didn't help
either.

The format is copying but only looking at the data in row 26.

Please help!!!