• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Formatting Macro

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
Thomask23
Asked:
Thomask23
  • 2
1 Solution
 
KnutsonBMCommented:
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
 
Thomask23Author Commented:
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
 
KnutsonBMCommented:
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

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now