CODEWITHSUNDEEP
excelms-accessvbaconditional-formatting
rpd
rpd

Reputation: 482

Excel 2010 VBA Conditional Formatting on Non-Blanks

So i am producing an excel workbook consisting of a number of sheets from Access. I want to apply gridlines (or border) ONLY on non-blank cells.

Doing it manually through excel is trivial, but the macro i recorder does not seem to work when i apply it on my code.

Any thoughts?

Upvotes: 0

Views: 3097

Answers (2)

Wolfie
Wolfie

Reputation: 30046

Old post but for future visitors here is an elegant solution to creating a conditional format for (non) blank cells in VBA...

If you want to add the format condition to selected cells:

' Add new condition to format blank cells. 
' For non-blanks use xlNoBlanksCondition
Selection.FormatConditions.Add Type:=xlBlanksCondition 

' Make it the first conditional format
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

' Apply formatting to condition
' Thin black border all around
With Selection.FormatConditions(1).Borders

    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin    

end With

Upvotes: 1

rpd
rpd

Reputation: 482

Sub Macro1()

ActiveWindow.DisplayGridlines = False
Cells.Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=LEN(TRIM(A1))>0"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Borders(xlLeft)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With

With Selection.FormatConditions(1).Borders(xlRight)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With

With Selection.FormatConditions(1).Borders(xlTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With

With Selection.FormatConditions(1).Borders(xlBottom)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With

Selection.FormatConditions(1).StopIfTrue = False

End Sub

Upvotes: 0

Related Questions