Conditional Formatting to color a cell if Offset 1 to the right of ActiveCell contains "XYZ"

[2Took]

Trying to get if not a solution, then thoughts on this endeavor:

To get a cell A formatted, if Offset 1 B to the right of ActiveCell C contains "XYZ".

A, B & C is to indicate three sells would be involved:
A is acted upon by Conditional Formatting,
B is cell evaluated if its value contains "XYZ", and
C is currently ActiveCell - as in one currently selected.

VBA solution is also welcome, but really curious about Conditional Formatting solution.
Or maybe a combo of the two?

 

[Joe4]

I don't understand column C's involvement here.
It sounds like you just want to Conditionally Format column A based on the values in column B.
So how does column C come in to play here?
Are you saying that you only want to format the active row, if the ActiveCell is in column C (so you would only ever format one row at a time)?

 

[2Took]

I don't understand column C's involvement here.
It sounds like you just want to Conditionally Format column A based on the values in column B.
So how does column C come in to play here?
Are you saying that you only want to format the active row, if the ActiveCell is in column C (so you would only ever format one row at a time)?

Hi @Joe4,

A, B, C are not columns, just various cells, which can be in any column. A, B & C is to indicate three sells would be involved.

Cell C is the ActiveCell. If one cell to the right of ActiveCell contains "XYZ", then cell A to be formatted.

 

[Joe4]

Cell C is the ActiveCell. If one cell to the right of ActiveCell contains "XYZ", then cell A to be formatted.

OK, so do you ONLY want to format based on the ActiveCelll (so only one cell would ever be formatted at a time), or are you looking to format ALL cells meeting the conditions?
And what is the entire range we would be applying/checking this to?
You don't want to unnecessarily apply Conditional Formatting to ALL columns/rows on a worksheet, as that will REALLY slow the performance of your sheet down.
So we really want to limit it to just the ranges we need to apply it to.

 

[2Took]

OK, so do you ONLY want to format based on the ActiveCelll (so only one cell would ever be formatted at a time), or are you looking to format ALL cells meeting the conditions?
And what is the entire range we would be applying/checking this to?
You don't want to unnecessarily apply Conditional Formatting to ALL columns/rows on a worksheet, as that will REALLY slow the performance of your sheet down.
So we really want to limit it to just the ranges we need to apply it to.

Only one cell would be formatted at a time, cell A.
Cell A formatted, if cell B - Offset 1 to the right of ActiveCell (cell C) contains "XYZ".

A, B & C is to indicate three sells would be involved:
A is acted upon by Conditional Formatting,
B is cell evaluated if its value contains "XYZ" - this is the cell that is Offset 1 to the right of ActiveCell (cell C)
C is currently ActiveCell - as in one currently selected.

 

[Joe4]

That is going to be a bit tricky/difficult to do because Conditional Formatting can only act on cell values - it cannot identify the "ActiveCell".
Which means VBA is probably going to be needed.
What makes VBA tricky is that as you move around from cell-to-cell, not only do you need to apply Conditional Formatting to the newly selected cell, but you need to remove it from all previously selected cells.

Assuming that there is no other Conditional Formatting anywhere else on your sheet, I can think of a way it can be done, but I still need those details I asked for in my previous reply:

And what is the entire range we would be applying/checking this to?

We need to know exactly which columns we would be applying this to. I doubt you want to apply it all the way out to column XFD!

 

[2Took]

That is going to be a bit tricky/difficult to do because Conditional Formatting can only act on cell values - it cannot identify the "ActiveCell".
Which means VBA is probably going to be needed.
What makes VBA tricky is that as you move around from cell-to-cell, not only do you need to apply Conditional Formatting to the newly selected cell, but you need to remove it from all previously selected cells.

Assuming that there is no other Conditional Formatting anywhere else on your sheet, I can think of a way it can be done, but I still need those details I asked for in my previous reply:

We need to know exactly which columns we would be applying this to. I doubt you want to apply it all the way out to column XFD!

What if VBA part be limited to printing ActiveCell's address in some utility cell? Say in W1
Conditional Formatting perhaps could pick up from there and accomplish the rest...

 

[Joe4]

I have a potential solution for you, if you would just answer the question I have asked you multiple times.

 

[2Took]

Let's say If ActiveCell is in Column A, only then see if Offset 1 to the right of ActiveCell contains "XYZ", and then format A* cell, which is acted upon by Conditional Formatting

*"A" in context of "A, B & C" above

 

[Joe4]

OK, you don't seem to want to answer my question, so I will just apply the code to the entire sheet.

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that appears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Clear conditional formatting from entire sheet
    Cells.FormatConditions.Delete

'   Exit if ActiveCell in columns A or B
    If ActiveCell.Column < 3 Then Exit Sub
  
'   Apply Conditional Formatting to cell two columns to left of ActiveCell
    With ActiveCell.Offset(0, -2)
'       Set formula
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=ISNUMBER(SEARCH(""XYZ""," & ActiveCell.Offset(0, -1).Address & "))"
'       Set formatting
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
    End With

End Sub

This code will run automatically as you select different cells on your sheet.

If this does not work the way you want, then you need to be more detailed in your explanation.
Walk us through an actual example giving EXACT cell addresses (not various words like A, B, and C, which really have no meaning).
If you can post an actual/real example, we should be able to clearly see exactly what you are after.