Conditional formating using relative references

[pcarmour]

Hi,
I have a code that works with an absolute formula to conditionally format cells that are zero in row 11 and have a value in row 10 but as these rows will continue to change I now need to use relative references and can't get it right. Please see attached. I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)
11604

[p45cal]

try:
Sub Relative2()
With Selection.Resize(, 20)
LowerCellAddress = .Cells(1).Address(False, False)
UpperCellAddress = .Cells(1).Offset(-1).Address(False, False)
.FormatConditions.Delete
With .FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(" & UpperCellAddress & ">0," & LowerCellAddress & "=0)")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With
End Sub


Since posting, it's easier than that; change your:
Selection.FormatConditions.Add Type:=xlExpression, FormulaR1C1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)"
to:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)"

so simplified a bit:
Sub Relative3()
With Selection.Resize(, 20).FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With
End Sub

[pcarmour]

HI p45cal,
Brilliant, that works perfectly. Thank you very much for your time and expertise.

Yet again VBA express has proved to be the place to get answers and get them quickly.
Thank you.

[snb]

In E11:

conditional format formula "=And(E$10>0,E$11=0)"
applies to $E$11:$X$11