Problem with relative reference.

[Roontoon]

New to the forum and I have a question regarding the macro below. I a
trying to create a macro that is generic in nature to be used in
financial work sheet but on any group of cells in the worksheet. I nee
the entire range to be generic which seems to be working but I am havin
a problem trying to figure out how to change the formula on line 3 to b
generic. The cell in question is three rows down and 16 columns from th
origination point. I am somewhat of a novice with actual editing o
macros so please be kind if this question is obvious. Thanks for you
help.


Code
-------------------

ActiveSheet.Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(0, 29)).Select
ActiveWindow.SmallScroll ToRight:=-6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$Q$114<0" <--------- This is my question.
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[joeu2004]

The cell in question is three rows down and 16 columns
from the origination point.

"16 columns __from__" does not tell us to the left or right. I will presume
16 to the right, since you are less likely to encounter boundary conditions.

Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$Q$114<0"

I'm not familiar with FormatConditions.Add per se. Based on your syntax,
the following should work for sure:

Formula1:="=" & Selection.Offset(3,16).Address & "<0"

But the following would be better if it works:

Formula1:="=R[3]C[16]<0"