Solved

Excel 2007:  Format Cells Outside of Selected Cells

Posted on 2012-03-23
2
233 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 41

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 41

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

803 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