Solved

Excel 2007:  Format Cells Outside of Selected Cells

Posted on 2012-03-23
2
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

688 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