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.
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.
Last edited by Terrific; 02-13-2020 at 01:46 PM.
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
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Thanks for the reply. I tried this and nothing happened plugged it in.
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Nor am I.
Trying to add a vid…
ok just a link then:
https://app.box.com/s/r8ptkxzpk5iqocxc28h9z3u8bqaz8yo3
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thanks very much Paul. That did the trick