More elegant way of replacing text and formatting it

Question

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!

Yes, good suggestion. Changed my project & thanks to you, L42! — BeachBum68, Jun 03 '14 at 15:37

score 1 · Answer 1 · answered May 20 '14 at 00:33

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

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

Thanks, Dan. That's a great routine! I'll add it to my Functions module and give it a go. It looks just right. — BeachBum68, Jun 03 '14 at 15:38
I hope it works out well... was fun to dig out a genuinely good question here! — Dan Wagner, Jun 06 '14 at 19:48

L42 · Accepted Answer · 2014-09-05T06:24:05.540

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.

edited Sep 05 '14 at 06:24

answered May 20 '14 at 06:54

L42

19,427
11
44
68

+1, this one does a better job of answering the actual question than my function, which requires the user to pass in a target `Range` to modify – Dan Wagner May 20 '14 at 10:54
Very nice! However, if I move/copy those cells to another sheet then wouldn't the formatting drop off? I need to test. Thanks for this code though, I'll definitely be using it! – BeachBum68 Jun 03 '14 at 15:35
@BeachBum68 No. You can copy and paste formatting as well. It is how you set it up that counts. – L42 Jun 04 '14 at 00:12
Ok, Thanks, L42. Noob mistake. I loved both your solution and Dan's and Dan's was the last one I read. I'll be using both solutions though in different parts of the project. Thanks again! – BeachBum68 Jun 10 '14 at 16:25

More elegant way of replacing text and formatting it

2 Answers2

Linked