Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range selection | Excel Discussion (Misc queries) | |||
Narrow Range based on Selection in Another Range | Excel Discussion (Misc queries) | |||
Identifying a selection of a selection of a range | Excel Worksheet Functions | |||
Range Selection | Excel Discussion (Misc queries) | |||
Selection Range | Excel Discussion (Misc queries) |