Solved

Redacting a row in Excel based on a term.

Posted on 2016-11-30
17
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 8

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 47

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 51

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 47

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 47

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 47

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 47

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 47

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 47

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 47

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 47

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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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