Highlight Print Area

Question

Hi 

I want to format a spreadsheet so that the print area has a different fill colour from the rest of the worksheet. I tried a conditional format based on a formula:

=ISNULL(A1 Print_Area)

But I get a message that intersections are not permitted in conditional formats.

What is the best way to do this?

Thanks

Vaughan

Answer 1

You could add a short User Definded Function in VBA that returns TRUE or FALSE depending upon where any cell is within a range. This would be used within Conditional Formatting to discern the Print_Area for an alternate Fill Colour.

Right click the sheet's tab and select View Code. Now that you are in the VB Editor, choose Insert, Module from the pull down menus. Paste this code in,

Function InRange(Range1 As Range, Range2 As Range) As Boolean

' returns True if Range1 is within Range2

    Dim InterSectRange As Range

    Set InterSectRange = Application.Intersect(Range1, Range2)

    InRange = Not InterSectRange Is Nothing

    Set InterSectRange = Nothing

End Function

... and then press ALT+Q to exit the VBE and return to Excel.

Now from anywhere in the sheet, press CTRL+A to Select All and then go to  the Home tab's Style group's Conditional Formatting, New Rule.

Click Use a formula to determine which cells to format, then paste this into Format values where this formula is true:,

=InRange(A1,Print_Area)

... and click the Format... button to select Fill, Border, etc that will highlight the Print_Area cells. Click OK on each window to save your settings. The Print_Area will be highlighted as you specified in a dynamic method.

---

• If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

Answer 2

You have the right idea, just the wrong syntax.  First install the following UDF:

Public Function WithinP(r As Range) As Boolean

WithinP = False

If Intersect(r, Range("Print_Area")) Is Nothing Then

    Exit Function

End If

WithinP = True

End Function

Then in A1 set Conditional formatting to Foluma:

=WithinP(A1)

and pick your formatting.

Then copy elsewhere.

---

gsnu201005

Answer 3

Hi 

I want to format a spreadsheet so that the print area has a different fill colour from the rest of the worksheet. I tried a conditional format based on a formula:

=ISNULL(A1 Print_Area)

But I get a message that intersections are not permitted in conditional formats.

What is the best way to do this?

Thanks

Vaughan 

You can use conditional formatting but you have to create some defined names first.

Try this...

***This is very important***

***Select cell A1***

Create this named formula ***while cell A1 is selected***...

• Goto the Formulas tab
• Defined Names
• Define Name
• Name: InRange
• Refers to: =NOT(ISERROR(Print_Area A1))
• OK out

Then apply the conditional formatting and use this formula:

=InRange

--

Biff

Microsoft Excel MVP

Answer 4

Both very helpful answers, thanks.

Vaughan