Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to wright a Conditional formatting macro for text

Posted on 2011-05-07
Medium Priority
Last Modified: 2012-06-27
I need a Conditional formatting macro to change the cell "blue" when the text says "Yes" in column L starting on cell L2.

I need a macro because I will copy it to 15 or more other columns. Each other column will look for other text conditions. Also this will be daily on a report.

Question by:eureka15
  • 5
  • 4
LVL 33

Accepted Solution

Robberbaron (robr) earned 2000 total points
ID: 35714581
the difficultly with NOT using Conditional Formatting is the requirement to change the code to suit each column.
And if you add a column Say E, then all the code needs to be updated to suit.

But macro attached  will do most of you request.

Column L = 12.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = "12" And Target.Row > 1 Then
        If Target.Value = "Yes" Then
            'format it
            With Target.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 12611584
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    End If
End Sub

Open in new window

LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 35714596
please give us some better examples of why Excel Conditional Formats wont work in your case as they are much easier to manage.
posting an example of your required formats to go with data is best.

you can use a formula in the Conditional formatting rule to do complex calcs if necessary,

Author Comment

ID: 35714606
Hi robberbaron:
The reason I don't think it will work is because:
1 It will take a lot of time to set each column for each report everyday
2 The end users are not that computer smart      
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 35714619
Sorry forgot to send file.
This is a quiz and the Cells in Blue are the right answers.
LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 35714990
either way, you will need to define the criteria for each column clearly.

for the macro method, you will need to create a new block of code for each column number.
LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 35714992
I would assume that each user will be starting with a template, with the criteria pre-loaded.

Author Comment

ID: 35715008
If you think a formula will work better I will go that way, If you don't mind making just one for column L?

No template will be used, the excel file is downloaded from a "form site" with the answer on in it.
LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 35715151
the code i posted is just for Column L.

but the code needs to be embeded within the excel file from the form site as it depends upon the Workbook_Change event.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35717138

Having users who are not Excel-savvy actually makes the case for using Conditional Formatting instead of a macro even stronger: for a macro-based approach to work, macros must be enabled, and since the last few versions of Excel have disabled macros by default, I would recommend using a non-code approach whenever it is viable.

Now, if what you mean is, "shade the row in blue if the entry in Column L = 'Yes'", that is very easy to do:

1) Select K2:AP58

2) Create a Conditional Formatting rule using the formula =$L2="Yes"   That $ is critically important

3) Choose your formatting.

That's it :)


Author Closing Comment

ID: 35719381

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

577 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