VBA - Selection Instead of Range
 

Hello,

The following code colors a row based on a cell value within that row.

Dim myCell2 As Range
Dim myrng2 As Range
Set myrng2 = Sheets("SORT").Range("$D$2:$D$240")

For Each myCell2 In myrng2.Cells

whatwant2 = myCell2.Value

For Each rcell In Sheets("SETUP").Range("$D$2:$D$20000")

If rcell.Value Like whatwant2 And rcell.Value 0 Then
rcell.EntireRow.Resize(1, 21).Interior.ColorIndex = 38
End If

Next rcell

Next myCell2


Instead of using Range for Set myrng2 & For Each rcell, I want the code to
use Selection. So if cells on both sheets, SORT & SETUP, I want the code to
search and color based on the selections. Can anyone show me how I can
accomplish this?

Thanks.
--
Cue

VBA - Selection Instead of Range
 

first i think you should chenge both range sets to use rnge names not cell
addrss. like-

Set myrng2 = Sheets("SORT").Range("SortSheetRg")

and

For Each rcell In Sheets("SETUP").Range("SetupSheetRg")


then befor you call this code you should create the names based on selection
in each sheet. like

sheets("sort").activate
selection.name = "SortSheetRg"
sheets("setup").activate
selection.name = "SetupSheetRg"


"Cue" wrote in message
...
| Hello,
|
| The following code colors a row based on a cell value within that row.
|
| Dim myCell2 As Range
| Dim myrng2 As Range
| Set myrng2 = Sheets("SORT").Range("$D$2:$D$240")
|
| For Each myCell2 In myrng2.Cells
|
| whatwant2 = myCell2.Value
|
| For Each rcell In Sheets("SETUP").Range("$D$2:$D$20000")
|
| If rcell.Value Like whatwant2 And rcell.Value 0 Then
| rcell.EntireRow.Resize(1, 21).Interior.ColorIndex = 38
| End If
|
| Next rcell
|
| Next myCell2
|
|
| Instead of using Range for Set myrng2 & For Each rcell, I want the code to
| use Selection. So if cells on both sheets, SORT & SETUP, I want the code
to
| search and color based on the selections. Can anyone show me how I can
| accomplish this?
|
| Thanks.
| --
| Cue

VBA - Selection Instead of Range
 

Thanks Homey!
--
Cue


"Homey" wrote:

first i think you should chenge both range sets to use rnge names not cell
addrss. like-

Set myrng2 = Sheets("SORT").Range("SortSheetRg")

and

For Each rcell In Sheets("SETUP").Range("SetupSheetRg")


then befor you call this code you should create the names based on selection
in each sheet. like

sheets("sort").activate
selection.name = "SortSheetRg"
sheets("setup").activate
selection.name = "SetupSheetRg"


"Cue" wrote in message
...
| Hello,
|
| The following code colors a row based on a cell value within that row.
|
| Dim myCell2 As Range
| Dim myrng2 As Range
| Set myrng2 = Sheets("SORT").Range("$D$2:$D$240")
|
| For Each myCell2 In myrng2.Cells
|
| whatwant2 = myCell2.Value
|
| For Each rcell In Sheets("SETUP").Range("$D$2:$D$20000")
|
| If rcell.Value Like whatwant2 And rcell.Value 0 Then
| rcell.EntireRow.Resize(1, 21).Interior.ColorIndex = 38
| End If
|
| Next rcell
|
| Next myCell2
|
|
| Instead of using Range for Set myrng2 & For Each rcell, I want the code to
| use Selection. So if cells on both sheets, SORT & SETUP, I want the code
to
| search and color based on the selections. Can anyone show me how I can
| accomplish this?
|
| Thanks.
| --
| Cue

.