Excel Autofilter & Color formatting thru vbscript

Posted on 2008-11-09
Last Modified: 2012-05-05

Would like to set autofilter & color formatting based thru vbscript.

for example: would like to set
1. Autofilter from column A to I
2. A - B color in yellow, C - D green in color etc

Please advise how can I achieve this thru vbscript

Thanks in advance
Question by:grind67
    LVL 46

    Expert Comment

    by:Wayne Taylor (webtubbs)
    VBA or VB Script?

    VBA (Visual Basic for Applications) is the code contained within MS Office applications.
    VB Script is usually run from .vbs files, or ASP.


    Author Comment

    Hi Wayne, it is vb script (not Visual basic for applicatons)

    If achieving thru VB script is NOT too hard then I would request this way
    Please advise alternate way?
    Basically, I create excel file thru an automated process (with header & data) and place it under a folder. If there is some mechanism (Windows/Microsoft office 2003 out of the box softwrae/Util) to apply this color format & autofilter...would try this way as well

    Unfortunetly, I cannot use VB 6


    Author Comment

    Fully automated solutions...right from the excel file creation to autofilter & color formatting

    Have done the Excel creation & sending email but only outstanding thing is autofilter & color formatting...I think it is bit tricky
    LVL 46

    Accepted Solution

    Given a Worksheet object "xlWS", use this to apply the AutoFilter and color the interior of the columns....

        xlWS.Range("A:B").Interior.ColorIndex = 6    'yellow
        xlWS.Range("C:D").Interior.ColorIndex = 4    'green

    Note: You may need to adjust the ColorIndex to match your color palette.


    Author Comment

    HI Wayne, that was excellent. Solved my problem...thanks a lot

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    VBA to sum a column 13 30
    Posting V12 2 16
    Simple Calculation for Value of Availablity 5 50
    VBA Help TT V-1.1 15 22
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now