3 thoughts on “SpecialCells limit problem”

1. We will have to continue with the looping codes . even when Excel 12 comes Out…..the limitation I understand will stay with Excel 12

   From the Excel 12 Blog

   “
   Wednesday, March 15, 2006 1:10 AM by Sam
   I know this is a bit OT.
   But one of the existing limitations that I am hopping will get removed is the ~9K non-conitnuous cells that you can select (and delete) at a time in 2003
   Is this in the pipe line

   Wednesday, March 15, 2006 12:15 PM by David Gainer
   Sam, unfortuantely this is not one that we changed, but it is on the list for things to think about next time.
   “

2. When writing VBA code I never use built-in function to locate cell or a specific Range. Special Cells and the Find function both can cause unwanted results. One example with find is that if you have the text “blah” in A1 and A2 and A1 is currently selected and you do a find for “blah” the FIRST result will be A2. I tried using the find function to loop through cells once and I did not get the order I wanted. I recommend just writing simple search functions instead of using these functions.

3. Hi Bill

   With code you can do this
   start after the last cell

   Sub Color_cells_in_Range()
   Dim FirstAddress As String
   Dim MySearch As Variant
   Dim myColor As Variant
   Dim rng As Range
   Dim I As Long

   MySearch = Array(“ron”)
   myColor = Array(“3?)

   ‘You can also use more values in the Array
   ‘MySearch = Array(“ron”, “jelle”, “judith”)
   ‘myColor = Array(“3?, “6?, “10?)

   With Sheets(“Sheet1?).Range(“B1:D100?)

   .Interior.ColorIndex = xlColorIndexNone
   ‘change the fill color to “no fill” in all cells

   For I = LBound(MySearch) To UBound(MySearch)
   Set rng = .Find(What:=MySearch(I), _
   After:=.Cells(.Cells.Count), _
   LookIn:=xlFormulas, _
   LookAt:=xlWhole, _
   SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, _
   MatchCase:=False)
   ‘If you want to find a part of the rng.value then use xlPart
   ‘if you use LookIn:=xlValues it will also work with a
   ‘formula cell that evaluates to MySearch(I)

   If Not rng Is Nothing Then
   FirstAddress = rng.Address
   Do
   rng.Interior.ColorIndex = myColor(I)
   Set rng = .FindNext(rng)
   Loop While Not rng Is Nothing And rng.Address FirstAddress
   End If
   Next I
   End With
   End Sub

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.