基于填充颜色和字体颜色的VBA格式单元格 [英] VBA format cell based on fill color and font color

问题描述

我必须具备的优势

1. 如果现有填充颜色为黄色，则删除单元格填充颜色

1. remove cell fill color if existing fill color is yellow

仅当现有字体颜色为红色时，才将单元格文本颜色设置回黑色.

set cell text color back to black only if existing font color is red.

我编写了一个宏，该宏只是在每个单元格上循环并检查字体颜色/填充颜色

I have written a macro that simply loops over each cell and checks the font color / fill color

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
...
For Each Cell In ws.UsedRange.Cells
   If Cell.Font.ColorIndex = 3 Then
      Cell.Font.ColorIndex = 0
   End If
   If Cell.Interior.ColorIndex = 6 Then
      Cell.Interior.Pattern = xlNone
      Cell.Interior.TintAndShade = 0
      Cell.Interior.PatternTintAndShade = 0
   End If
Next

它可以按预期工作，但运行速度很慢，可能是因为它穿过了每个单元格.是否有使这项工作更快的方法?我尝试在VBA中使用条件格式，但是它似乎无法检查单元格颜色/单元格字体颜色...

It works as expected but it runs very slowly probably because it goes through each cell. Is there a to make this work faster ? I tried using conditional formatting with VBA but it cant seem to check for the cell color / cell font color...

推荐答案

无需循环.您可以使用颜色进行搜索和替换.试试这个

No Need to loop. You can search and replace using colors. Try this

如果现有填充颜色为黄色，则删除单元格填充颜色

With Application.FindFormat.Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Application.ReplaceFormat.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

如果您必须手动执行此操作，则可以完成此操作

If you had to do this manually, you would have done this

类似字体.

仅当现有字体颜色为红色时，才将单元格文本颜色设置为黑色.

With Application.FindFormat.Font
    .Subscript = False
    .Color = 255
    .TintAndShade = 0
End With
With Application.ReplaceFormat.Font
    .Subscript = False
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
End With
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

如果您必须手动执行此操作，则可以完成此操作

If you had to do this manually, you would have done this

注意:VBA Find使用参数.除What:=外，其余参数都是可选的.建议您提供这些参数.如果您不这样做，则Find将使用现有设置.如果不想提供可选参数，则必须在使用Find之前清除这些参数，否则会得到不希望的结果.您可以通过Application.FindFormat.Clear

Note: The VBA Find uses parameters. Except What:=, rest of the parameters are optional. It is recommended that you provide those parameters. If you don’t, then Find will use the existing settings. If you do not want to provide the optional parameters then it is a must to clear those parameters before you use Find else you will get undesired results. You can do that by Application.FindFormat.Clear

类似地，Replace使用参数，如果您不想提供可选参数，请使用Application.ReplaceFormat.Clear

Similarly Replace uses parameters and if you do not want to provide the optional parameters then clear them using Application.ReplaceFormat.Clear

这篇关于基于填充颜色和字体颜色的VBA格式单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！