Conditional Formatting - Skip empty cells/values

1
votes

I'm new to macros. I created a very simple macro which allows a range of cells to be formatted within a worksheet. The macro works fine within the worksheet however when I save the document as a csv file there are several trailing commas. I know that the trailing commas are a result of formatting that occurred with the empty values of the worksheet. I need the macro to only format the cells that contain data, but I also need to keep the range of cells. Is this possible? I've tried conditional formatting, CountA formulas, IsEmpty macros,and Is/Then/Else macros but nothing seems to work. I included an example of the data that I have been working with, any help will be appreciated. 

Sub FormatDollarAmount()

    Set MyCellRange = Range("F2:F51")

    If (Not (IsEmpty("F2:F51"))) Then

        Range("F2:F51").NumberFormat = "#,##0.00;_(@_)"

    Else

    End If

End Sub
2
excelvba
Read the answer below. Your code is only going to work if there are no values in the entire range. If there is a single value in any of the cells it will apply the format to the entire range. You need to loop through each cell. – Evan Callahan

2 Answers

0
votes

As long as this is titled regarding conditional formatting, here is a conditional formatting response that won't require re-running every time the data changes.

with worksheets("sheet1")
    with .range(.cells(2, "F"), .cells(.rows.count, "F").end(xlup))
        .formatconditions.delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=sign(len($f2))"
        .FormatConditions(.FormatConditions.Count).numberformat = "#,##0.00;_(@_)"
    end with
end with
0
votes

You can loop through the cells:

Sub FormatDollarAmount()
    Set MyCellRange = Range("F2:F51")
    For each myCell in myCellRange
        If (Not (IsEmpty(myCell))) Then
            myCell.NumberFormat = "#,##0.00;_(@_)"
        Else

        End If
    Next myCell
End Sub