If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
For each this, then that and that
If I drop the "X" part, the below will shade the cells I want. I just can't
get the syntax for adding - ActiveCell.Offset(0, 1) = "X". I have tried several versions of this with only undesired results. Any help would be appreciated. Thank in advance wal50 Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1:A20", Type:=8) rng.Select For Each Cell In Selection If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 And ActiveCell.Offset(0, 1) = "X" Next Cell End Sub |
#2
|
|||
|
|||
For each this, then that and that
Maybe just changing:
ActiveCell.Offset(0, 1) = "X" to Cell.Offset(0, 1).Value = "X" Since you're not activating/selecting the cell when you loop through them. (I like specifying the property, too.) wal50 wrote: If I drop the "X" part, the below will shade the cells I want. I just can't get the syntax for adding - ActiveCell.Offset(0, 1) = "X". I have tried several versions of this with only undesired results. Any help would be appreciated. Thank in advance wal50 Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1:A20", Type:=8) rng.Select For Each Cell In Selection If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 And ActiveCell.Offset(0, 1) = "X" Next Cell End Sub -- Dave Peterson |
#3
|
|||
|
|||
For each this, then that and that
Try this - it worked for me
Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1: A20 ", Type:=8) For Each Cell In rng If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 Cell.Offset(0, 1) = "X" End If Next Cell End Sub There is no need to do the Select; it did not change the active cell as you went through the loop best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "wal50" wrote in message ... If I drop the "X" part, the below will shade the cells I want. I just can't get the syntax for adding - ActiveCell.Offset(0, 1) = "X". I have tried several versions of this with only undesired results. Any help would be appreciated. Thank in advance wal50 Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1:A20", Type:=8) rng.Select For Each Cell In Selection If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 And ActiveCell.Offset(0, 1) = "X" Next Cell End Sub |
#4
|
|||
|
|||
For each this, then that and that
Thank you gentlemen. This a valuable and educational resource.
"Bernard Liengme" wrote: Try this - it worked for me Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1: A20 ", Type:=8) For Each Cell In rng If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 Cell.Offset(0, 1) = "X" End If Next Cell End Sub There is no need to do the Select; it did not change the active cell as you went through the loop best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "wal50" wrote in message ... If I drop the "X" part, the below will shade the cells I want. I just can't get the syntax for adding - ActiveCell.Offset(0, 1) = "X". I have tried several versions of this with only undesired results. Any help would be appreciated. Thank in advance wal50 Sub MarkCells() Dim Cell As Range Dim MarkThis As String MarkThis = Application.InputBox("Enter Value to Mark", Type:=2) Set rng = Application.InputBox("Enter Range to check - A1:A20", Type:=8) rng.Select For Each Cell In Selection If Cell.Value = MarkThis Then Cell.Interior.ColorIndex = 4 And ActiveCell.Offset(0, 1) = "X" Next Cell End Sub |
Thread Tools | |
Display Modes | |
|
|