更优雅的替换文本和格式化方式

Right now I have this:

现在我有这个:

[M3].select 'Range("M3").Select originally, I like using the [ ] notation
totalrows = [H2].CurrentRegion.Rows.Count
Range("m3:p" & totalrows).Select
[m2].Activate
'Green
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
    End With
    Selection.Replace What:="Green", Replacement:="Red", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=True

Is there a more elegant way of accomplishing this?

是否有更优雅的方式来实现这一目标?

I found this today and I love it's simplicity and have incorporated it in other sections that deal with text replacements.

我今天发现了这一点,我喜欢它的简单性,并将其纳入其他处理文本替换的部分。

[M:P].Replace "Green", "Red", xlPart

But, is there an easy way of setting the cell background color to green with a simple statement like that? If the cell contains the text "Green", change the cell background to Green and don't change the text. Thanks for your suggestions!

但是,是否有一种简单的方法可以将单元格背景颜色设置为绿色,并使用这样的简单语句?如果单元格包含文本“绿色”,请将单元格背景更改为绿色,不要更改文本。谢谢你的建议!

2 个解决方案

#1

If the cell contains the text "Green", change the cell background to Green and don't change the text.

如果单元格包含文本“绿色”,请将单元格背景更改为绿色,不要更改文本。

I'd use this which I think is elegant enough:

我会用这个我觉得很优雅的:

[A1].FormatConditions.Add xlExpression, , "=A1=""Green"""
With [A1].FormatConditions(1)
    .Interior.Color = RGB(0, 255, 0)
    .ModifyAppliesToRange [M:P] '~~> of course change this part to suit
End With

Not one liner though.

虽然不是一个班轮。

#2

Stumbled upon this one collecting dust and thought a little helper routine would do the trick nicely. ApplyBackgroundColor is nothing fancy -- a Select...Case statement with a tiny morsel of error trapping. Here is a link to the colors I used, feel free to build out more functionality: http://dmcritchie.mvps.org/excel/colors.htm

偶然发现这个收集灰尘,并认为一个小帮手例行将很好地完成这个伎俩。 ApplyBackgroundColor没什么特别的 - 一个Select ... Case语句带有一小部分错误捕获。这是我使用的颜色的链接,随意构建更多功能:http://dmcritchie.mvps.org/excel/colors.htm

Option Explicit
Public Sub ApplyBackgroundColor(Target As Range, Color As String)
'error trap, if target is empty then exit
If Target Is Nothing Then Exit Sub
With Target
    Select Case UCase(Color)
        Case Is = "GREEN"
            .Interior.ColorIndex = 4
        Case Is = "RED"
            .Interior.ColorIndex = 3
        Case Is = "BLUE"
            .Interior.ColorIndex = 5
        Case Is = "YELLOW"
            .Interior.ColorIndex = 6
        Case Else '<~ don't do anything if the string doesn't match
    End Select
End With
End Sub

And here's the obligatory test routine to make sure it works:

这是必须的测试程序,以确保它的工作原理:

Sub TestApplyBackgroundColor()

Dim MyRange As Range
Set MyRange = Range(Cells(1, 1), Cells(5, 5))
Call ApplyBackgroundColor(MyRange, "Blue") '<~ not the most elegant, but an improvement

End Sub

#1