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

  • Thread starter Thread starter Dan Thompson
  • Start date Start date
D

Dan Thompson

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
 
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
 
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
 
OssieMAc answered....

For Each cel In ActiveSheet.UsedRange
If cel.Interior.ColorIndex = 8 Then cel.Interior.ColorIndex = xlNone
Next cel
 
Your code work Patrick but unfortunitly it is still just as slow as mine and
takes forever to finish
 
Back
Top