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]

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…
https://app.box.com/s/r8ptkxzpk5iqocxc28h9z3u8bqaz8yo3

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

[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