[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Autofilter & Color formatting thru vbscript

Posted on 2008-11-09
5
Medium Priority
?
929 Views
Last Modified: 2012-05-05
Hi,

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
0
Comment
Question by:grind67
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22915694
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.

Wayne
0
 

Author Comment

by:grind67
ID: 22915947
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
OR
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

thanks
0
 

Author Comment

by:grind67
ID: 22917729
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
0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 22918800
Given a Worksheet object "xlWS", use this to apply the AutoFilter and color the interior of the columns....

    xlWS.Range("A:I").AutoFilter
    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.

Wayne
0
 

Author Comment

by:grind67
ID: 22920660
HI Wayne, that was excellent. Solved my problem...thanks a lot
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

830 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