PDA

View Full Version : Solved: Change Cell Colors By CF



Wolfgang
09-05-2007, 12:59 PM
Hi All...

I have a range of numbers in column A down to cell A113
Using Conditional Formatting manually does the trick
Using some profound code below will color ALL cells when fired...

Where should I be embarrassed???!!!

Best,
Wolfgang


Sub COLOR_BY_CF()
Range("A2").Select
Range("=OFFSET($A$2,,,COUNTA($A:$A)-1,1)").FormatConditions.Delete
Range("=OFFSET($A$2,,,COUNTA($A:$A)-1,1)").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUM(N($A$1:$A1<>$A$2:$A2)),2)"
Range("=OFFSET($A$2,,,COUNTA($A:$A)-1,1)").FormatConditions(1).Interior.ColorIndex = 36
End Sub

rory
09-05-2007, 03:50 PM
I think the array formula is the issue - try this:
Sub COLOR_BY_CF2()
Range("A2").Select
With Range("=OFFSET($A$2,,,COUNTA($A:$A)-1,1)").FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=MOD(SUMPRODUCT(N($A$1:$A1<>$A$2:$A2)),2)"
.Item(1).Interior.ColorIndex = 36
End With
End Sub

Wolfgang
09-06-2007, 02:59 AM
Hi Rory,

that's it...

Thank you very much and have some beer(s) on me...

Best,
Wolfgang

Wolfgang
09-06-2007, 11:30 AM
Hi again, Rory...

Someone came up with a solution using FREQUENCY instead of SUMPRODUCT.

Do you think that was a smart one or rather clumsy...

Please give me your opinion about that...

Best,
Wolfgang




Sub cf_with_frequency()
Range("A2").Select
With Range("=OFFSET($A$2,,,COUNTA($A:$A)-1,1)").FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=MOD(SUM(N(FREQUENCY($A$2:$A2,$A$2:$A2)>0)),2)>0"
.Item(1).Interior.ColorIndex = 36
End With
End Sub

rory
09-07-2007, 01:45 AM
They both work just as well so it's just a question of which you find easier to understand really.