删除特定颜色的所有单元格 [英] delete all cells of a certain color

问题描述

这似乎相对简单，据我了解，这是可能的。但是我似乎无法弄清楚或找不到我在互联网上正在寻找的东西。

This seems relatively simple and as I understand, it is possible. But I can't seem to figure it out or find exactly what I am looking for on the internet.

我在A列中有一些excel数据，其中一些数据是蓝色（0,0,255），有些是红色（255,255,255），有些是绿色（0，140，0）。我想删除所有蓝色数据。

I have some excel data in column A and some of the data is blue (0,0,255), some is red (255,255,255), some is green (0, 140, 0). I want to delete all blue data.

有人告诉我：

Sub test2()
    Range("A2").DisplayFormat.Font.Color
End Sub

会给我颜色...但是当我运行它时，它说该属性的使用无效并突出显示.color

Would give me the colors... but when I run that it says invalid use of the property and highlights .color

相反，我单击了的：
字体颜色下降
然后是更多颜色
然后是自定义颜色
然后我可以看到蓝色的数据为（0,0,255）

Instead I clicked on the: Font color drop down then more colors then custom colors then I can see that the data in blue is at (0,0,255)

所以我尝试了：

Sub test()

Dim wbk As Workbook
Dim ws As Worksheet
Dim i As Integer
Set wbk = ThisWorkbook
Set ws = wbk.Sheets(1)

Dim cell As Range

With ws
    For Each cell In ws.Range("A:A").Cells
        'cell.Value = "'" & cell.Value
        For i = 1 To Len(cell)
            If cell.Characters(i, 1).Font.Color = RGB(0, 0, 255) Then
                If Len(cell) > 0 Then
                    cell.Characters(i, 1).Delete
                End If
                If Len(cell) > 0 Then
                    i = i - 1
                End If
            End If
        Next i
    Next cell
End With

End Sub

我在网上找到了这个解决方案，但是在运行时，似乎什么也没有

I found this on the web as a solution in several places but when I run it, nothing seems to happen.

推荐答案

这是基本操作，如果未删除具有蓝色字体的单元格，则该字体是另一种颜色。更改范围以满足您的需求。

This is basic, if your cells with blue font are not deleted then the font is a different color. Change the range to meet your needs.

For Each cel In ActiveSheet.Range("A1:A30")
    If cel.Font.Color = RGB(0, 0, 255) Then cel.Delete
Next cel

已更新，允许用户选择带有字体颜色的列中的第一个单元格，获取字体颜色，并清除所有与字体颜色匹配的单元格。

Updated to allow user to select the first cell in the column with the font color, obtain the font color, and clear all the cells that match the font color.

Dim rng As Range
Set rng = Application.InputBox("Select a Cell:", "Obtain Range Object", Type:=8)

    With ActiveSheet
        Dim lr As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row

        Dim x As Long
        x = rng.Row

        For i = lr To x Step -1
            If .Cells(i, 1).Font.Color = rng.Font.Color Then .Cells(i, 1).Clear
        Next i
    End With 

这篇关于删除特定颜色的所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！