Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Redacting a row in Excel based on a term.

Posted on 2016-11-30
17
Medium Priority
?
76 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 9

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 49

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 53

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 49

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 49

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 49

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 49

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 49

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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

604 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