PDA

View Full Version : Use a dynamic range in a formula



Djblois
01-08-2009, 12:12 PM
I am using some code to band rows but now I am switching it to be dynamic. Here is the code I am trying but I cant figure out how to do it dynamically:

With Range(Cells(lgStart, 1), Cells(finalrow(ActiveSheet), FinalColumn(ActiveSheet)))
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(MOD(ROW(),2),COUNTA($A1:$H2))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.ColorIndex = 34
.StopIfTrue = False
End With
End With
I know how to do it in code but I cant do it in the formula portion. This portion I dont know how to make dynamic:

COUNTA($A1:$H2))

I need to make it so the column can always be different

Bob Phillips
01-08-2009, 04:46 PM
Is this what you mean Daniel?

=COUNTA($A1:INDEX(2:2,COUNTA(1:1)))