Redact Rows Based Upon Cell Contents

[lneidorf]

Hi there.

I've played around with this problem and had no success so far. A search of this forum yielded nothing.

I've got a some large spreadsheets with confidential information. I need to clear the contents and redact rows where cells contain specified text.

I'd like some VBA code that will do the following:
1. Look through the active area for specific text strings;
2. Where there's a match, clear the content of the entire row AND shade the row black (not then entire row, just the columns in use).

For example, my used range is A1:H200. Cells C15, D20, and F50 = "PHI".
As a result, I'd like to clear all contents of Rows 15, 20, and 50 AND shade with black fill A15:H15, A20:H20, & A50:H50.

Thanks!

 

[Nishant94]

Are you looking for something like this:

Sub ClearCon()


Dim rng As Range


Do
    Set rng = Range("A1:H200").Find(what:="PHI", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    If Not rng Is Nothing Then
        With Range(Cells(rng.Row, "A"), Cells(rng.Row, "H"))
            .ClearContents
            .Interior.Color = vbBlack
        End With
    End If
Loop Until rng Is Nothing


End Sub

 

[Fluff]

Another option

Sub lneidorf()
   With Range("A2:H200")
      .Replace "PHI", True, xlWhole, , False, , False, False
      Intersect(.SpecialCells(xlConstants, xlLogical).EntireRow, Range("A:H")).Interior.Color = vbBlack
      .SpecialCells(xlConstants, xlLogical).EntireRow.ClearContents
   End With
End Sub