Excel Redaction / Cell Formatting Macro for Mouse Selected Area
top of page
Search
    • Aug 13, 2016

Excel Redaction / Cell Formatting Macro for Mouse Selected Area


PLEASE NOTE: USING THE REDACTION MACRO DEMONSTRATED IN THIS VIDEO WILL NOT NECESSARILY GUARANTEE THAT THE SELECTED TEXT CAN'T BE RECOVERED UNLESS THE SPREADSHEET IS CONVERTED TO AN IMAGE OR OTHER STEPS ARE TAKEN.

The purpose of the macro used in this demonstration posted to my YouTube channel is to allow a user to select a range of cells with his or her cursor and then have the macro automatically replace the selected range with a merged cell filled in black and captioned, 'Redacted'.

The general format of the macro can be used to apply any formatting information to a range selected by a user with a mouse.

1. To begin click on the View tab on the ribbon, and then select Macros . . . View Macros

2. Type in a name for the macro and then click 'Create'

3. Use this VBA code.

Sub redactrange() Dim Specifiedrange As Range Set Specifiedrange = Application.InputBox("Specifiy the range to be named: 'Table'", Type:=8) MsgBox "This is the range you selected " & Specifiedrange.Address Specifiedrange.MergeCells = True Specifiedrange.Font.Color = vbWhite Specifiedrange.Interior.ColorIndex = 1 Specifiedrange.Value = "Redacted"

Specifiedrange.VerticalAlignment = xlCenter End Sub

The first part of the VBA code lets you determine the 'Specifiedrange'. This 'Specifiedrange' is then referenced when setting formatting information in the second part of the code. You can add more formatting information at the end of the code by typing Specifiedrange followed by a command.

4. You can go back to View Macros to add a shortcut command to activate the macro.

5. Now when we press CTRL + j the macro will prompt us to select a range of text.

6. Click okay after entering your selection. A message will confirm the selected area, and another message will remind you that the values in the selected cells will be lost after they are merged.

7. We end up with a nice black box, captioned 'Redacted'.


bottom of page