Solved

Redacting a row in Excel based on a term.

Posted on 2016-11-30
17
56 Views
Last Modified: 2016-12-02
I have a set of 20 Excel files that need to have information redacted from them.  For the purpose of redaction, I just need to change the background and text to black.

For each row in my worksheet, I want to search for the text ‘Acme’.  If ‘Acme’ can’t be found in any field for a given row, I want to redact the entire row.

Does anyone know of the best way to do this?  I know that Conditional Formatting, Macros and COM automation are all options.  I am hoping someone can give me some direction to limit my research time.

Thank you in advance.
0
Comment
Question by:rye004
17 Comments
 
LVL 6

Expert Comment

by:Mike in IT
ID: 41908666
Using VBA, you could search each cell going through them one row at a time and if the "Acme" is not in the row then change the back ground color and text color. Maybe something like this:

Sub SearchAcme()
Dim RowNumber, CountRows
CountRows = Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
RowNumber = 1
For j = RowNumber To CountRows Step 1
    For i = 1 To 100
        Range(i & j).Select
        If Range(i & j).Value = "Acme" Then
            Range(i & ":" & i).EntireRow.Interior.ColorIndex = 1
            Range(i & ":" & i).EntireRow.Font.Color = vbBlack
        End If
    Next j
Next i
End Sub

Open in new window


This is untested code though.
1
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41908938
You should also protect (lock) the redacted cells and protect the worksheet with the "Select locked cells" option turned off, otherwise selecting the cell will show the text in the formula bar.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41909028
Hi,

pls try
Sub BlackenAcme()
Dim rw
ActiveSheet.Unprotect
Cells.Locked = False
For Each rw In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    If Evaluate("=Countif(" & rw.EntireRow.Address & ",""acme"")") Then
        rw.EntireRow.Interior.ColorIndex = 1
        rw.EntireRow.Locked = True
    End If
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

Regards
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:rye004
ID: 41909623
I am still having issues with this.  I could not get either to work above.  I tried the following, with no luck.

Sub BlackenAcme()
Dim rw
ActiveSheet.Unprotect
Cells.Locked = False
For Each rw In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    
    If (Range(rw.EntireRow.Address).Find(What:="acme", MatchCase:=False) = 0) Then
        rw.EntireRow.Interior.ColorIndex = 1
        rw.EntireRow.Locked = True
    End If
    
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909637
You seem to want to look in just column "A" is that right?
Sub BlackenAcme()
Dim rw As Range
Dim rngFound As Range
ActiveSheet.Unprotect
Cells.Locked = False
For Each rw In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    
    Set rngFound = rw.EntireRow.Find(What:="acme", MatchCase:=False)
    If Not rngFound Is Nothing Then
        rw.EntireRow.Interior.ColorIndex = 1
        rw.EntireRow.Locked = True
    End If
    
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909639
If you want to look for "acme" everywhere then change line 6 to
For Each rw In ActiveSheet.UsedRange

Open in new window

0
 

Author Comment

by:rye004
ID: 41909654
Thank you so much Martin, that was a huge help.  Below is what I have now, I am going to try it on the rest of my Excel sheets:

Sub BlackenAcme()
Dim rw As Range
Dim rngFound As Range
ActiveSheet.Unprotect
Cells.Locked = False
For Each rw In ActiveSheet.UsedRange
    
    Set rngFound = rw.EntireRow.Find(What:="acme", MatchCase:=False)
    'If Not rngFound Is Nothing Then
    If rngFound Is Nothing Then
        rw.EntireRow.Interior.ColorIndex = 1
        rw.EntireRow.Locked = True
    End If
    
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909657
Changing line 9 the way you did will blacken the rows that don't contain acme.
0
 

Author Comment

by:rye004
ID: 41909661
That is the behavior I was looking for.  I might have miss spoken in my original posting, but thanks for checking.

I do have another question if you don’t mind.  Is it possible to blackout only the Used Range instead of the entire row?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909671
??I'm confused by your two responses. If a sheet looks like this, which cells or rows should be black?
0
 

Author Comment

by:rye004
ID: 41909673
rows 2,3,4,5,6,8 and 9 would be blacked out.

It would be great if column E and F were not blacked out, since they are not in the used range.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909676
Rows 8 and 9 are not in the used range either. Do you still want them blackened?
0
 

Author Comment

by:rye004
ID: 41909678
You are correct.  8 and 9 are not in the used ranged, so I would not want them blacked out.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909686
Sub BlackenAcme()
Dim rw As Range
Dim rngFound As Range
ActiveSheet.Unprotect
Cells.Locked = False
For Each rw In ActiveSheet.UsedRange
    
    Set rngFound = rw.EntireRow.Find(What:="acme", MatchCase:=False)
    If rngFound Is Nothing Then
        With Range("A" & rw.Row & ":" & Split(Cells(1, ActiveSheet.UsedRange.Columns.Count).Address, "$")(1) & rw.Row)
            .Interior.ColorIndex = 1
            .Locked = True
        End With
    End If
    
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41909694
Forget my previous post. This will be faster and easier to understand. (BTW you might want to change the name of the macro since that's not what we're doing.)
Sub BlackenAcme()

Dim rw As Range
Dim rngRows As Range
Dim rngFound As Range

ActiveSheet.Unprotect
Cells.Locked = False

Set rngRows = ActiveSheet.UsedRange
For Each rw In rngRows.Rows
    
    Set rngFound = rw.Find(What:="acme", MatchCase:=False)
    If rngFound Is Nothing Then
        With rw
            .Interior.ColorIndex = 1
            .Locked = True
        End With
    End If
    
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Open in new window

0
 

Author Closing Comment

by:rye004
ID: 41909755
Thanks again for your help with this Martin.  It is truly appreciated.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41910314
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

803 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