Solved

Excel 2007:  Format Cells Outside of Selected Cells

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now