Solved: Change Cell Colors By CF

[Wolfgang]

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

[vba]
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
[/vba]

[rory]

I think the array formula is the issue - try this:
[VBA]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
[/VBA]

[Wolfgang]

Hi Rory,

that's it...

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

Best,
Wolfgang

[Wolfgang]

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

[VBA]
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
[/VBA]

[rory]

They both work just as well so it's just a question of which you find easier to understand really.