I want to add a conditional format to every cell in the range A1:C3
where the formula in the conditional format references the cell 10
rows below the current cell, for instance for cell A1 the formula is:

=A1<>A11

and the formula for B1 is:

=B1<>B11

and so on.

I have the following loop to create the conditional formats but I
don't know how to create the formula to reference the cells correctly
as the loop executes.

For Each c In Range("A1:C3)
c.FormatConditions.Delete
c.FormatConditions.Add Type:=xlExpression, _
Formula1:="=A1<>A11
c.FormatConditions(1).Interior.ColorIndex = 36
Next c

How do I use the right variable so the correct formulas are created?

TIA



John Keith
(E-Mail Removed)

Assuming you want relative referencing, try it this way...

For Each C In Range("A1:C3")
C.FormatConditions.Delete
C.Select
C.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & C.Address(0, 0) & "<>" & C.Offset(10).Address(0, 0)
C.FormatConditions(1).Interior.ColorIndex = 36
Next

--
Rick (MVP - Excel)


"John Keith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I want to add a conditional format to every cell in the range A1:C3
> where the formula in the conditional format references the cell 10
> rows below the current cell, for instance for cell A1 the formula is:
>
> =A1<>A11
>
> and the formula for B1 is:
>
> =B1<>B11
>
> and so on.
>
> I have the following loop to create the conditional formats but I
> don't know how to create the formula to reference the cells correctly
> as the loop executes.
>
> For Each c In Range("A1:C3)
> c.FormatConditions.Delete
> c.FormatConditions.Add Type:=xlExpression, _
> Formula1:="=A1<>A11
> c.FormatConditions(1).Interior.ColorIndex = 36
> Next c
>
> How do I use the right variable so the correct formulas are created?
>
> TIA
>
>
>
> John Keith
> (E-Mail Removed)

Rick,

Thank you, I think that is the new process I needed to learn.

>Assuming you want relative referencing, try it this way...
>
>For Each C In Range("A1:C3")
> C.FormatConditions.Delete
> C.Select
> C.FormatConditions.Add Type:=xlExpression, _
> Formula1:="=" & C.Address(0, 0) & "<>" & C.Offset(10).Address(0, 0)
> C.FormatConditions(1).Interior.ColorIndex = 36
>Next

John Keith
(E-Mail Removed)