highlight selected cells in excel and blackout others for sharing

  • Thread starter Thread starter Brian Pruett
  • Start date Start date
B

Brian Pruett

This is a suggestion for excel. I would like to be able to highlight certain
cells and click a button or right click and choose an option that would black
out the other cells. Example: I am showing an employee their information on a
payroll spreadsheet which also contains other employee's confidential
information.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
If you can make use of macros in your environment, the following subroutine
can be attached to a CommandButton placed on the worksheet (or a button
placed on the toolbar even) or simply executed via pressing Alt+F8...

Sub ToggleItems()
Dim C As Range
If Selection.Count = 1 Then Exit Sub
If IsNull(Worksheets("Sheet1").UsedRange.Interior.ColorIndex) Then
Worksheets("Sheet1").UsedRange.Interior.ColorIndex = xlNone
Else
For Each C In Worksheets("Sheet1").UsedRange
If Intersect(C, Selection) Is Nothing Then
If Len(C.Value) Then C.Interior.Color = C.Font.Color
End If
Next
End If
End Sub

The way it works is if you have more than one cell selected, all the
non-selected cells will be blacked-out (have their interior color changed to
the same color as the font's color). When activated a second time, the macro
will remove all the black-outs. Note... you will not want to leave your
employee alone with the worksheet as selecting the blacked out cells will
make their content readable.

You could even activate the macro via a right mouse click using event code
something like this...

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim C As Range
If Selection.Count > 1 Then
Cancel = True
If IsNull(Worksheets("Sheet1").UsedRange.Interior.ColorIndex) Then
Worksheets("Sheet1").UsedRange.Interior.ColorIndex = xlNone
Else
For Each C In Worksheets("Sheet1").UsedRange
If Intersect(C, Selection) Is Nothing Then
If Len(C.Value) Then C.Interior.Color = C.Font.Color
End If
Next
End If
End If
End Sub

although, as written, the above will remove the normal right click
functionality when more than one cell is selected.

Rick
 
Perhaps a simple Data>Filter>Autofilter would suffice?


Gord Dibben MS Excel MVP

On Sat, 5 Apr 2008 22:53:01 -0700, Brian Pruett <Brian
 
Perhaps a simple Data>Filter>Autofilter would suffice?

Gord Dibben  MS Excel MVP





- Show quoted text -
Click to expand...

I agree with Gord Dibben, just Filter it before your customer arrives
then its all in place for you to show them. Much quicker and simpler.
 
Back
Top