Conditional Formatting based on another column

[pkew22]

I have ID's in a column (currently Col K) and Codes currently in Column M. (Columns will be added but Codes will always be two columns to the right of ID.
I have the following code but how do I use Offset in the formula to look for an entry two columns to the right?

I don't want the formula to say look for Col M but to look at the data two columns to the right and see if there is an X there.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$M4=""X"""

The larger recorded macro is:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$M4=""X"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99481185338908E-02
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Thank you for any help.

 

[Joe4]

If you select the ENTIRE range you want to apply it to first, but then write the Conditional Formatting formula as it applies to the very first cell in your selection, Excel will automatically adjust it for all the other cells in your selection.

So, if you start in cell K4, and select your entire range, and then write your CF formula like this:

=M4="X"

it should do what you want.

Note how I do NOT have a "$" in front of the column letter "M", so it will NOT be locked down and will automatically "float" two columns to the right for all other columns in your selected range).
So, cell L4 will look at O4, etc.

 

[Fluff]

How about

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=rc[2]=""X"""

 

[pkew22]

Thank you for your responses Joe4 and Fluff. I really appreciate your time and willingness to help. Fluff's response was perfect. Thank you for teaching me this. It is so helpful as this situation is going to repeatedly come up.

 

[Joe4]

You are welcome. Glad we could help.

Also note what I said in my reply, as that may come in handy in the future. You can apply CF to a whole range of cells at once, and depending on whether you lock down your cell references or not, the conditions will automatically "float" (or not).

Using the Format Painter can be kind of fickle. I usually have much more luck applying the formatting to a whole range at once, rather than writing it for one cell, and then trying to use the Format Painter.

 

[pkew22]

Yes, Joe4. I saw your tip. I have never used this since I always wanted to keep the column locked while the row changed. But I will keep this in mind. Once again, thank you for your kindness in responding to my desperate query and for your time.