[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional format based upon value - in VBA

Posted on 2011-05-02
5
Medium Priority
?
240 Views
Last Modified: 2012-05-11
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
Comment
Question by:tbaseflug
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
royhsiao earned 2000 total points
ID: 35505909
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35506143
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
 

Author Comment

by:tbaseflug
ID: 35506274
royhsiao - this seems to work - but is there anyway to do it on workbook open, etc.?
0
 

Author Comment

by:tbaseflug
ID: 35506731
OK - I have it working on workbook open - how do I put the font in red and the background just normal?
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35507241
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question