CODEWITHSUNDEEP
excelconditional-formattingvba
user2532928
user2532928

Reputation: 69

Have conditional formatting macro skip blanks in Excel

I have using the Recorder Macro the following:

 Application.ScreenUpdating = False

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=0", Formula2:="=19.5"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
        .Bold = False
        .Italic = True
        .ColorIndex = 4

    End With
  Selection.FormatConditions(1).StopIfTrue = True

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=19.6", Formula2:="=34.4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
        .Bold = False
        .Italic = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
    Selection.FormatConditions(1).StopIfTrue = False

With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Selection.FormatConditions(1).StopIfTrue = False

Then I use a macro to cut all the conditions and leave only the formatting. However, no matter what I did, Isblank, adding another conditional formatting conditions to only run on non blanks, after the conditional formatting macro, the formatting is green (which turns any 0-19.5 green, but there was nothing in the cell).

Is there a way to add a skip line to this macro? If it is blank I want it to move to the next cell. I don't have set ranges so that's why it's all on selection.

Upvotes: 2

Views: 1837

Answers (3)

Rich Harding
Rich Harding

Reputation: 635

I came across a similar issue with conditional formatting myself, with cells that are either blank or have a string value, where I wished to ignore the former.

I found that conditional formatting functions do not work correctly with ADDRESS(), but that I could write user-defined functions to use with =AND(), which got around the issue.

Public Function CellContent() As Variant

  On Error Resume Next

  CellContent = Application.Caller.value

End Function

Public Function Cell_HasContent() As Boolean

  On Error Resume Next

  If Application.Caller.value = "" Then
    Cell_HasContent = False
  Else
    Cell_HasContent = True
  End If

End Function

The Resume Next statements are critical. If you're trying to check that any non-blanks have a value of say, 2, you can now check this with:

Formula1:="=AND(CellContent()=2,CellHasContent())"

Upvotes: 0

teylyn
teylyn

Reputation: 35915

You can loop through each cell in the selection and only apply the format if the cell is not blank.

Option Explicit

Sub test()

Dim cel As Range

Application.ScreenUpdating = False
On Error Resume Next

For Each cel In Selection
    If cel <> "" Then

    cel.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=0", Formula2:="=19.5"
  cel.FormatConditions(cel.FormatConditions.Count).SetFirstPriority
  With cel.FormatConditions(1).Font
        .Bold = False
        .Italic = True
        .ColorIndex = 4

    End With
  cel.FormatConditions(1).StopIfTrue = True

    cel.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=19.6", Formula2:="=34.4"
    cel.FormatConditions(cel.FormatConditions.Count).SetFirstPriority
  With cel.FormatConditions(1).Font
        .Bold = False
        .Italic = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
    cel.FormatConditions(1).StopIfTrue = False

With cel
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
cel.FormatConditions(1).StopIfTrue = False

End If

Next cel
Application.ScreenUpdating = True
End Sub

Upvotes: 2

HighHopes
HighHopes

Reputation: 2102

If the formatting is all that you want, then use the macro that you have recorded here and simply copy the code that causes the formatting. 

 With Selection.FormatConditions(1).Font
        .Bold = False
        .Italic = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
 End With

This block of code seems to have some of the formatting that you might want.

Also, because you are working with a selection instead of each cell individually, checking for a blank cell would be more difficult. As far as I know, you can't do it because you are treating the selection as a whole range. A cell is a range as well. Someone correct me if I am wrong.

Upvotes: 0

Related Questions