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

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.

A really lazy (=inefficient) way:

Code:

---

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

---

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

Quote:

---

Originally Posted by Terrific

plugged it in.

---

What does this mean?


You have to run it.
Otherwise attach a workbook.

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

Delete the Option Explicit line.

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.

Put this in the worksheet code module

Code:

---

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

---

Nor am I.
Trying to add a vid…
https://app.box.com/s/r8ptkxzpk5iqocxc28h9z3u8bqaz8yo3

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

Quote:

---

Originally Posted by 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.

---

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

Code:

---

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

---

Thanks very much Paul. That did the trick