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

Is there a better way to get Range of cells for entier worksheet ?
 

Use a named Range?

--

Regards,
Nigel




"Dan Thompson" wrote in message
...
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

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

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

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

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