Conditional Formatting in a Loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.....
 
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
 
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?
 
PLEASE DISREGARD MY LAST MESSAGE.

IT WORKS OKAY NOW.

THANKS SO MUCH FOR YOUR 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?
 
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
 
Back
Top