add cell into a range based on criteria
Posted by Artem on April 10, 2001 12:56 PM
hello,
a simple problem. i have a commercial macro which runs only on selected cells, so what i need is a way to select those cells that i need. basically i have a column with criteria, and 2 columns to the left another column in which i need to select cells. So say if the cell in criteria column equals "Excluded" then i don't do anything, otherwise i include the cell 2 cells to the left in my dynamic range. then i run my macro on the active dynamic range. maybe it is possible to just select a cell based on criteria and keep it selected while another one is being selected too (kind of like selecting a range while holding Ctrl key)? the code below doesn't work (because it's wrong). thanks for help!
Sub Update()
Dim DynamicRange As Range
Range("Criteria").Select
For Each cell In Selection
If cell.Value <> "Excluded" Then
DynamicRange = Union(DynamicRange, cell.Offset(-2, 0).Address)
End If
Next cell
Range("DynamicRange").Activate
' Here i run my update macro on dynamic range
End Sub