VBA Conditional formatting - multiple adjacent cells if specific text on left

[Terrific]

If a cell contains "z" then highlight the 3 cells to the right of it. Example If C4=z then C4, D4, E4, F4 turn yellow. J5=z then J5, K5, L5, M5 turn yellow. For my application I need to use VBA not regular conditional formatting.

[p45cal]

A really lazy (=inefficient) way:

Sub blah()
For Each cll In ActiveSheet.UsedRange
  If cll.Value = "z" Then cll.Resize(, 4).Interior.Color = vbYellow
Next cll
End Sub

[Terrific]

Thanks for the reply. I tried this and nothing happened plugged it in.

[p45cal]

plugged it in.

What does this mean?


You have to run it.
Otherwise attach a workbook.

[Terrific]

Attached is the file with what you sent, but isn't working. Let me know what I might be missing.

[p45cal]

Delete the Option Explicit line.

[Terrific]

That's how I first put it in and it didn't work. I deleted Option Explicit again, but it still isn't working. Not sure what I'm doing wrong.

[Paul_Hossler]

That's how I first put it in and it didn't work. I deleted Option Explicit again, but it still isn't working. Not sure what I'm doing wrong.

If you don't want to use a WS change event, then I'd put it in a standard module, not the WS code module

Option Explicit


Sub blah()
    Dim cll As Range
    
    On Error GoTo NiceExit
    For Each cll In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        If cll.Value = "z" Then cll.Resize(, 4).Interior.Color = vbYellow
    Next cll
    
NiceExit:
End Sub

[Terrific]

Thanks very much Paul. That did the trick

[Paul_Hossler]

Put this in the worksheet code module

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    
    On Error GoTo NiceExit
    For Each r In Target.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        If r.Value = "z" Then r.Resize(1, 4).Interior.Color = vbYellow
    Next


NiceExit:
End Sub

[p45cal]

Nor am I.
Trying to add a vid…


ok just a link then:
https://app.box.com/s/r8ptkxzpk5iqocxc28h9z3u8bqaz8yo3