Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier worksheet ?
I am wondering if there is a quick easy way to set a range for all the cells
in a worksheet. for example.. Set X = Range("Sheet1") I want to run a conditional statement on each cell in my work sheet somthing like ... For each cel in (x) if cel.interiorcolor = 5 Then (run this code) I would rather use somthing like Set X = Range("Sheet1") than have to do something like Set X = Range("Sheet1").cells(1,1) to (65536, 256) Any thoughts please ? Thanks Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier worksheet ?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier worksheet ?
Hi Dan,
Set x = Sheets("Sheet1").Cells but I wonder why you want to test the entire worksheet. The following sets the used range on the worksheet. Set x = Sheets("Sheet1").UsedRange -- Regards, OssieMac "Dan Thompson" wrote: I am wondering if there is a quick easy way to set a range for all the cells in a worksheet. for example.. Set X = Range("Sheet1") I want to run a conditional statement on each cell in my work sheet somthing like ... For each cel in (x) if cel.interiorcolor = 5 Then (run this code) I would rather use somthing like Set X = Range("Sheet1") than have to do something like Set X = Range("Sheet1").cells(1,1) to (65536, 256) Any thoughts please ? Thanks Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier worksheet ?
I thought my question needed to be simpler so here is my working code what it
does is checks all cells in the active worksheet for any cells colored Cyan if there are it replaces thoes cell with the "no fill" color the problem is when I run the code it works but is VERY VERY slow and takes forever to finish running on one sheet. Dim EntireSheet As Range, cel As Range With ActiveSheet Set EntireSheet = Range(.Cells(1, 1), .Cells(65536, 256)) For Each cel In EntireSheet If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone Next cel End With Is there a more efficient way of doing this ?? Dan "Dan Thompson" wrote: I am wondering if there is a quick easy way to set a range for all the cells in a worksheet. for example.. Set X = Range("Sheet1") I want to run a conditional statement on each cell in my work sheet somthing like ... For each cel in (x) if cel.interiorcolor = 5 Then (run this code) I would rather use somthing like Set X = Range("Sheet1") than have to do something like Set X = Range("Sheet1").cells(1,1) to (65536, 256) Any thoughts please ? Thanks Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier workshe
OssieMAc answered....
For Each cel In ActiveSheet.UsedRange If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone Next cel "Dan Thompson" wrote: I thought my question needed to be simpler so here is my working code what it does is checks all cells in the active worksheet for any cells colored Cyan if there are it replaces thoes cell with the "no fill" color the problem is when I run the code it works but is VERY VERY slow and takes forever to finish running on one sheet. Dim EntireSheet As Range, cel As Range With ActiveSheet Set EntireSheet = Range(.Cells(1, 1), .Cells(65536, 256)) For Each cel In EntireSheet If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone Next cel End With Is there a more efficient way of doing this ?? Dan "Dan Thompson" wrote: I am wondering if there is a quick easy way to set a range for all the cells in a worksheet. for example.. Set X = Range("Sheet1") I want to run a conditional statement on each cell in my work sheet somthing like ... For each cel in (x) if cel.interiorcolor = 5 Then (run this code) I would rather use somthing like Set X = Range("Sheet1") than have to do something like Set X = Range("Sheet1").cells(1,1) to (65536, 256) Any thoughts please ? Thanks Dan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a better way to get Range of cells for entier workshe
Your code work Patrick but unfortunitly it is still just as slow as mine and
takes forever to finish "Patrick Molloy" wrote: OssieMAc answered.... For Each cel In ActiveSheet.UsedRange If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone Next cel "Dan Thompson" wrote: I thought my question needed to be simpler so here is my working code what it does is checks all cells in the active worksheet for any cells colored Cyan if there are it replaces thoes cell with the "no fill" color the problem is when I run the code it works but is VERY VERY slow and takes forever to finish running on one sheet. Dim EntireSheet As Range, cel As Range With ActiveSheet Set EntireSheet = Range(.Cells(1, 1), .Cells(65536, 256)) For Each cel In EntireSheet If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone Next cel End With Is there a more efficient way of doing this ?? Dan "Dan Thompson" wrote: I am wondering if there is a quick easy way to set a range for all the cells in a worksheet. for example.. Set X = Range("Sheet1") I want to run a conditional statement on each cell in my work sheet somthing like ... For each cel in (x) if cel.interiorcolor = 5 Then (run this code) I would rather use somthing like Set X = Range("Sheet1") than have to do something like Set X = Range("Sheet1").cells(1,1) to (65536, 256) Any thoughts please ? Thanks Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best Substitute for Range(cells(),cells()) in worksheet | Excel Programming | |||
Pop Up Calendar for entier workbook | Excel Worksheet Functions | |||
Unlocking range of cells/worksheet? | Excel Discussion (Misc queries) | |||
cells name or range name in a worksheet | Excel Programming | |||
is there a way to protect a range of cells on a worksheet? | Excel Discussion (Misc queries) |