Solved

Formatting Macro

Posted on 2011-02-17
3
296 Views
Last Modified: 2012-06-21
Hello
I need a macro that will filter on a field and highlight cells that have a value greater that 0 for specific cells. I have attached an example.  In the example the macro need to do the following:
1.Filter on the field titled "Paid", where the field "Paid" = Yes.  
2.Highlight the cells in blue where the values in the cells are greater than 0 for the columns with the titles (G1,G3,G5,G7)

Thanks
Test.xlsm
0
Comment
Question by:Thomask23
  • 2
3 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
Comment Utility
This should do the trick:

Sub FilterAndHilight()

    ActiveSheet.Range("$A:$L").AutoFilter Field:=4, Criteria1:="Yes"
    m = 8
    Do Until Cells(m, 1).Value = ""
        If Cells(m, 5).Value > 0 Then
            Cells(m, 5).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
        Else
        End If
           If Cells(m, 7).Value > 0 Then
            Cells(m, 7).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
        Else
        End If
        If Cells(m, 9).Value > 0 Then
            Cells(m, 9).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
        Else
        End If
        If Cells(m, 11).Value > 0 Then
            Cells(m, 11).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
        Else
        End If
        m = m + 1
    Loop
End Sub
0
 

Author Comment

by:Thomask23
Comment Utility
Thanks...this is really good. This almost works.
However I have vlookups in each cell.  I need to highlight cells that have a value returned from the vlookup > 0.

Thanks again for your help.
0
 
LVL 6

Accepted Solution

by:
KnutsonBM earned 500 total points
Comment Utility
which cells are your v-lookups in?  I opened up your sample sheet and do not see any formulas.  have you considered using Conditional Formatting?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

11 Experts available now in Live!

Get 1:1 Help Now