Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2007:  Format Cells Outside of Selected Cells

Posted on 2012-03-23
2
Medium Priority
?
245 Views
Last Modified: 2012-04-19
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
0
Comment
Question by:southray
  • 2
2 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37759536
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
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37759562
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question