Thread: Relative cell reference in macro

I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum. If there is a more elegant way to accomplish this please feel free to advise.

Thanks

If I understand your problem, instead if defining RangeStart as =$B$19, define it instead as =INDEX($B:$B, ROW($B$18) + 1). That means it always starts one row beneath the header.

You want cells that contain the "PLD" highlighted?

Select A1 and do Insert > Name > Define Ductwork!Me refers to =A1 (no dollar signs). Now Me always refers to the cell in which the reference appears.

Then change the code like this:

Sub PLD()
    With Range("RangeStart").Offset(1, 0)
        With .FormatConditions
            .Delete
            .Add Type:=xlExpression, Formula1:="=SEARCH(""PLD"",Me,1) > 0"
            .Item(1).Interior.ColorIndex = 36
            .Add Type:=xlExpression, Formula1:="=SEARCH(""NA"", Me,1) > 0"
            .Item(2).Interior.ColorIndex = 15
        End With

        .Copy
        Range("RangeStart:RangeEnd").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                                  SkipBlanks:=False, Transpose:=False
    End With
    Range("RangeStart").Select
    Application.CutCopyMode = False
End Sub

Me is a named range, just like RangeStart, except instead of being defined in absolute position, it's defined relative to the cell in which it appears. It's convenient enough that I have it in my standard template.

Here's another example of a relative named range. Select cell A2, and do Insert > Name > Define, relUp refers to =A1

Then in some arbitrary cell (say C10), you can enter =SUM(C5:relUP). If you insert a row above the formula, the new row will be captured in the total.

Absolutely. For now, select each worksheet and do insert>name>define sheetName!Me as referring to the cell you have selected with NO $ signs.

For the future, create a blank workbook with a single sheet, the styles and font size you like, and any other customizations (e.g., color palette) you wish. Then select A2 and define

Sheet1!Me Refers to: =A2

Sheet1!relUp Refers to =A1

conBig Refers to: =9.99999999999999E+307

conZzz Refers to: žžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžž žžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžž žžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžž

The last two names are named constants with workbook (not worksheet) scope. That space among the z's above is a forum artifact.

Then save in your XLSTART directory as Book.xlt, and again in the same directory as Sheet.xlt.

Book.xlt is the default template when you add a workbook (Ctrl+n), and Sheet.xlt when you insert a worksheet.

When you do Insert > Name > Define, and preface the range name with the sheet name, the name has sheet scope. Without, it has workbook scope. You need to wrap your brain around this. This is unrelated to relative and absolute references, so for an example, we'll use absolute references.

Create a workbook scratch.xls with four sheets.

Select Sheet1 and define Sheet1!Joe refers to =$A$1 (SHEET scope)

Select Sheet2 and define Sheet2!Joe refers to =$B$2 (SHEET Scope)

Select Sheet3 and define Joe refers to =$C$3 (WORKBOOK scope)

Then ponder Joe used in various ways on various sheets:

Formula is On   Reference Is   Refers To
-------------  --------------  ---------
   Sheet1                 Joe  Sheet1!A1
   Sheet2                 Joe  Sheet2!B2
   Sheet3                 Joe  Sheet3!C3
   Sheet4                 Joe  Sheet3!C3
                                         
   Any sheet       Sheet2!Joe  Sheet2!B2
   Any sheet       Sheet3!Joe  Sheet3!C3
   Any sheet  scratch.xls!Joe  Sheet3!C3

My example was to illustrate the difference between sheet and workbook scope for a name.

On Sheet1, select A1 and do insert > name > define, Sheet1!Me refers to =A1

On Sheet2, select A1 and do insert > name > define, Sheet2!Me refers to =A1

...

On SheetN, select A1 and do insert > name > define, SheetN!Me refers to =A1

Now Me ALWAYS refers to the cell where the reference is made.

After you get it correctly in your Book and Sheet templates, you'll never have to do it again.


If you're having trouble getting this set up in this particular workbook, try this instead:

Delete name Me from ALL sheets. Then select ANY sheet, select A1, and do Insert > Name > Define, Me refers to =!A1

That will create the name for all sheets.

I see what you mean; it doesn't like Me defined that way. That's a curious aberration of Conditional Formatting.

OK, back to the prior method. On each sheet, define SheetName!Me