Using Excel VBA to search for "Filled" cell


I'm using VBA in Excel 2010, and what seems like it should be a straightforward problem is turning out to have a wrinkle somewhere.  My client uses a "Fill" to highlight the cells that he wants to allow users to edit.  Everything not so shaded should be protected from editing.

The method for doing this is to start by protecting every cell, then search for the proper highlight and unprotect those.  By setting a HoldRange at the start of the search, I am assured that the procedure will go through its paces only once and then stop, because it will come back around on itself.  I then manually protect the sheet, at which point I can edit anything that's appropriate for me to do so, and I get an appropriate error message when attempting to edit a non-highlighted cell.  Although this process works, the catch is that when I record a macro to do the search and then run that search, the search fails, mysteriously.  As long as I "seed" it with a proper manual search, the code below works as advertised:

Sub find_Highlight_And_Unlock(UseSheet As Worksheet)
    Dim UseRange As Excel.Range
    Dim HoldRange As Excel.Range
    Dim lngCount As Long
    Set UseRange = UseSheet.Range("A1")
    UseSheet.Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True).Activate
    Set HoldRange = UseSheet.Parent.activeRange
    HoldRange.Locked = False
    HoldRange.FormulaHidden = False
    ActiveSheet.Cells.Locked = True
    While (HoldRange.Address <> UseRange.Address)
        Set HoldRange = _
            Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True).Activate
        Selection.Locked = False
        Selection.FormulaHidden = False
End Sub

Open in new window

When I record myself searching for a highlight, VBA autogenerates this code, which doesn't succeed in finding anything when run:

With Application.FindFormat
    With .Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99481185338908E-02
        .PatternTintAndShade = 0
   End With
End With

Open in new window

I would like for my code to work as a standalone, without the manual "seeding".  In fact, even better would be if my code could open the dialogue box that allows me to set the search format at runtime and then unlock the relevant highlighted cells.  Writing code to open the search dialogue box would be more intuitive and flexible than what I have now: a fixed highlight that doesn't work anyway.  

Any thoughts on how to do this?  ~Peter Ferber
Who is Participating?
Robberbaron (robr)Connect With a Mentor Commented:
what about the user selecting any one of the desired formatted cells ?

  You can return a range object with the users selections and get the desired format from it.
Upload a sample, with few rows of data.
Specify the requirement referring to the sample.
PeterFrbAuthor Commented:
Actually, I've made progress on my question through independent research.  This link does well in addressing my question:

The key piece of code is this one:


Open in new window

Having come this far, the difficulty I now have is that the code following this routine runs before I've made the selection here,  I would prefer that the code following this routine wait until I've finished filling in the search parameter before running, but that is not happening.  The following command opens a find dialogue and holds the code execution until I've said "OK", but that dialogue only allows for the searching of text, not formatting it.


Open in new window

My holy grail is the "Show" facility with a dialogue that allows me to format cells.  Between the two, I have those two features, but not in the same dialogue.  The work shows undeniable progress, though, for which I'm grateful.  ~Peter
PeterFrbAuthor Commented:
Yes, I got this working, and that's what I did.  I do prefer, when you give a suggestion, that you give a basic rundown of how to do that thing.  Sincerely, ~Peter
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.