|
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
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!!! |
Conditional Formatting in a Loop
WORKED PERFECTLY!!!! THANKS SO MUCH FOR YOUR HELP!!!!
"Tom Ogilvy" wrote: 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!!! |
| All times are GMT +1. The time now is 01:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com