Conditional Formatting Macro - grey out blank cells

[mjd]

Hi,

I'm working on a project (in excel 2010) that reviews financial data for hundreds of accounts on a daily, weekly, monthly, quarterly and yearly basis. I've got the analytics all squared away, but the sheet is massive and is printed so that the board can review things. some accounts have not been open long enough to have monthly/yearly data, and I'd like to grey out those cells. The conditional formatting I've tried to incorporate into my macro is as follows:

    Range("H10:AD150").Select
    Range("AD10").Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(AD10))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Now, this fails in many ways. Greys out non-blank cells, leaves some blank cells without a background.

here is the full code:

Sub format()
    'ChDir "C:\"
    'ActiveWorkbook.SaveAs Filename:= _
        "C:\temp.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
    Columns("B:AE").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 1
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Cells.FormatConditions.Delete
    Rows("9:59").Select
    ActiveWorkbook.Worksheets("Muni").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Muni").Sort.SortFields.Add Key:=Range("M10:M59"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Muni").Sort
        .SetRange Range("A9:AH59")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    Rows("9:9").Select
    Selection.EntireRow.Hidden = True
 
    Rows("10:59").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW()-9,1*2)+1<=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 45
 
    Range("AG10").Select
    ActiveCell.Formula = "=M10&"" bps"""
    Range("AG10").Select
    Selection.AutoFill Destination:=Range("AG10:AG59"), Type:=xlFillDefault
    Range("AG10:AG59").Select
    Selection.Copy
    Range("M10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Range("H10:AD59").Select
    Range("AD10").Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(AD10))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 
End Sub

Any ideas? The data changes often and the sort in the macro makes greying things out manually a foolish use of time.

Thanks,
Mike

 

[VoG]

Why not use Conditional Formatting http://www.contextures.com/xlCondFormat01.html

If you want to do this in VBA all that selecting is unnecessary - see http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

 

[Marcelo Branco]

Maybe this

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM([B][COLOR=blue]$[/COLOR][/B]AD10))=0"

M.

 

[mjd]

Hey,

Marcelo, I took your idea and ran with it. This variation of the code worked perfectly:

    Range("h10").Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(h10))=0"

Thanks for your input!