Excel 2007: Format Cells Outside of Selected Cells

I have some Excel 2007 forms where I want to remove all the borders from the empty cells around the main working area of the form.  I would like to do something like select the important cells and then format all of the non-selected cells but I have not found a way to do this.

Thanks
southrayAsked:
Who is Participating?
 
dlmilleCommented:
Use the following function to get the inverted Range based on selecting, using UsedRange (as opposed to the entire workbook:

A simplified - but standard - version that should work for your purposes.  See the test macro and attached workbook.

Option Explicit

Function RangeComp(rngA As Range, rngB As Range) As Range
    ' Returns the Relative Complement of rngA in rngB
    ' RangeComp = rngB - RngA

    Dim cell    As Range

    If rngA Is Nothing Then
        Set RangeComp = rngB
    
    ElseIf rngB Is Nothing Then
        ' nothing to do; will return Nothing
    Else
        For Each cell In rngB
            If Intersect(cell, rngA) Is Nothing Then
                If RangeComp Is Nothing Then
                    Set RangeComp = cell
                Else
                    Set RangeComp = Union(RangeComp, cell)
                End If
            End If
        Next
    End If
End Function

Sub test() 'after having selected a range
Dim rng As Range

    Cells.Interior.Color = -4142
    Set rng = RangeComp(Selection, ActiveSheet.UsedRange)
    rng.Interior.Color = vbYellow
End Sub

Open in new window


See attached.

Enjoy!  I had a lot of fun with variations on this about a year ago, lol.
http://www.experts-exchange.com/Microsoft/Applications/Q_26758836.html

Dvae
rangeCompliment-r1.xls
0
 
dlmilleCommented:
You want to format all non-selected cells - do you realize formatting the entire worksheet with that exception can take time and resource and potentially make your workbook large?

Or do you want to format non-selected cells but within a used range of cells?  That's do-able.

Please advise, and I'll whip out my complementary macro which inverts the selection based on that criteria.

Dave
0
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.