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

Thanks to jindon for posting the first part of the code at this forum: http://www.mrexcel.com/forum/excel-questions/235355-visual-basic-applications-code-select-range-using-mouse-hilighting-range.html

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'.

VBA Code to Redact Specified Range in Excel

Here's a little VBA code of my own devising to redact a range of cells in an Excel spreadsheet.

We begin with a spreadsheet that looks like this:

Press ALT + F11 and then insert a module with this visual basic code:

Sub textcolor() Range("A1:A3").MergeCells = True Range("A1:A3").Font.Color = vbWhite Range("A1:A3").Interior.ColorIndex = 1 Range("A1").Value = "Redacted" End Sub

Run the macro and we get a redacted range:

Getting a count of the number of worksheets in a spreadsheet

Follow the instructions on this site, in order to get a count of the number of worksheets in a spreadsheet.

Go to Formulas tab and in the 'Defined Names' section select 'Define Name'

In the dialog box that appears use the name 'countsheets' and then enter the formula =GET.WORKBOOK(1)&T(NOW()) in the 'Refers to' box.

Now you can just enter the formula, =COUNTA(INDEX(countsheets,0)) to get a sheet count.