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, _
    ‘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
    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.

Leave a Reply

Your email address will not be published.