ExcelBanter - Problem with relative reference.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with relative reference. (https://www.excelbanter.com/excel-programming/446023-problem-relative-reference.html)

Problem with relative reference.

New to the forum and I have a question regarding the macro below. I am trying to create a macro that is generic in nature to be used in a financial work sheet but on any group of cells in the worksheet. I need the entire range to be generic which seems to be working but I am having a problem trying to figure out how to change the formula on line 3 to be generic. The cell in question is three rows down and 16 columns from the origination point. I am somewhat of a novice with actual editing of macros so please be kind if this question is obvious. Thanks for your 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

Problem with relative reference.

"Roontoon" wrote:
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.

"Roontoon" wrote:
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 su

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

But the following would be better if it works:

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