• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Conditional format based upon value - in VBA

I have a series of cells with values like:

{i}99000
97110
{i}85025

What I would like to do is to conditionally format based upon the presence of {i} - additionally, would like to remove the {i} - if I could do it in a cell formula/conditional formatting great - if not, in VBA?
0
tbaseflug
Asked:
tbaseflug
  • 2
  • 2
1 Solution
 
royhsiaoCommented:
Please check the following code.
Sub Macro1()
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192 'you could update the color here
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Replace What:="{i}", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
End Sub

Open in new window

0
 
Patrick MatthewsCommented:
tbaseflug,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
0
 
tbaseflugAuthor Commented:
royhsiao - this seems to work - but is there anyway to do it on workbook open, etc.?
0
 
tbaseflugAuthor Commented:
OK - I have it working on workbook open - how do I put the font in red and the background just normal?
0
 
royhsiaoCommented:
Please try this out.
Sub Macro3()

    With Application.ReplaceFormat.Font
        .FontStyle = "Bold"
        .Size = 12
        .Superscript = False
        .Subscript = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    Cells.Replace What:="{i}", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now