CODEWITHSUNDEEP
vbaexcel
Sean_myers
Sean_myers

Reputation: 137

if Font.Color is not black then delete cell

I'm having an issue that I cannot understand, this is the script:

Sub WorksheetLoop()

   Dim WS_Count As Integer
   Dim I As Integer
   Dim ws As String
   Dim sht As Worksheet

   ' Set WS_Count equal to the number of worksheets in the active
   ' workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count
   ws = ActiveSheet.Name

   ' Begin the loop.
   For I = 1 To WS_Count

        For Each sht In ActiveWorkbook.Worksheets
            ws = Worksheets(I).Name
            Dim x As Range
            Set x = Worksheets(I).UsedRange
            x.ClearComments

            x.Replace What:="*€£$*", Replacement:=""

            For Each Cell In Worksheets(I).UsedRange
                If x.Font.Color <> Black Then
                    x.Delete
                End If
            Next
        Next sht

   Next I

End Sub

All of the code works with exception to:

For Each Cell In Worksheets(I).UsedRange
    If x.Font.Color <> Black Then
          x.Delete
    End If
Next

I've tried changing this to equaling black, this still doesn't work. I've tried stepping through at that point, it never finds a cell that would qualify to delete the cell.

Upvotes: 0

Views: 2575

Answers (4)

Gary
Gary

Reputation: 1

Don't use Cell.Delete, beacuse you will recognize, your VBA will copy a cell instead of a not black cell. This is a delete and copy. You have to use, Cell.ClearContents. And it will be delete, the cell contents!

Upvotes: 0

Brian M Stafford
Brian M Stafford

Reputation: 8868

You need to refer to Cell within your loop. Like this:

For Each Cell In Worksheets(I).UsedRange
    If Cell.Font.Color <> vbBlack Then
          Cell.Delete
    End If
Next

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

In your loop For Each Cell In Worksheets(I).UsedRange, you are looping through the Range with Cell (Range object), and afterwards you are checking if If x.Font.Color <> Black Then instead of If Cell.Font.Color <> Black Then.

Also, the Cell.Font.Color returns a numeric value, it should comapre against vbBlack and not Black.

Modified Code

Option Explicit

Sub WorksheetLoop()

Dim WS_Count As Long
Dim I As Long
Dim ws As String
Dim sht As Worksheet
Dim x As Range ' define outside the loop
Dim C As Range

' Set WS_Count equal to the number of worksheets in the active workbook
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
    With Worksheets(I)
    'For Each sht In ActiveWorkbook.Worksheets ' <-- No need for this loop
        ws = .Name

        Set x = .UsedRange
        x.ClearComments

        x.Replace What:="*€£$*", Replacement:=""

        For Each C In x ' I replaced Cell with C (Cell is too close to Cells)
            If C.Font.Color <> vbBlack Then
                C.Delete
            End If
        Next C
'        Next sht
    End With

Next I

End Sub

Upvotes: 0

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

You cannot refer to colors directly in VBA. Instead, you need to work with their numeric reference. Since black is 0, your code ought to look something like this

For Each x In Worksheets(I).UsedRange
    If x.Font.Color = 0 Then
          x.Delete
    End If
Next

I've also changed For Each Cell to x because that's the variable you've defined in your code. And in this instance you want to test if the cell is equal to black, rather than not equal to

Upvotes: 0

Related Questions