Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I reference cells in CF formula within loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I reference cells in CF formula within loop
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I reference cells in CF formula within loop
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to reference two cells to construct formula | Excel Programming | |||
Loop for formula on one sheet with reference from another sheet | Excel Programming | |||
Putting formula in cells from a loop | Excel Programming | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
My formula screws up other reference cells... | Excel Discussion (Misc queries) |