CODEWITHSUNDEEP
vbaexcel
Edward Armstrong
Edward Armstrong

Reputation: 125

Replace all numbers in spreadsheet below certain value with "<1%" -EXCEL VBA

I need to write a macro that looks for values between .005 and 0 and replaces them with "<1%".

I wrote a macro that adds conditional formatting to change these values to red text. I can't figure out how to modify my formula to change these values to "<1%".

'

     Cells.Select
      Range("F3").Activate
       Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
       Formula1:="=0", Formula2:="=0.005"
     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
    .Color = -16776961
    .TintAndShade = 0
     End With
      Selection.FormatConditions(1).StopIfTrue = False
     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
   End With
    Selection.FormatConditions(1).StopIfTrue = False
 End Sub

Upvotes: 0

Views: 1901

Answers (2)

richardtallent
richardtallent

Reputation: 35374

Rather than a macro, try using this custom format:

[<0]-0.000;[<=0.005]"<1%";0.000

This will format (not changing the value) of the cell to show three decimal places for numbers < 0 and > 0.005, and the phrase "<1%" for the ones within that range.

Then, you can apply a normal conditional formatting rule to the entire range of cells, no macro required. The conditional format rule works against the actual value of the cell, not the formatted "<1%", so that rule should look for numbers between 0 and 0.005 inclusive.

If you want red text for these (not a red background color), you can even avoid the need for a conditional format by using this custom format:

[<0]-0.000;[Red][<=0.005]"<1%";0.000

Upvotes: 4

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Select the cells you wish to modify and run this as a separate macro:

Sub Fixup()
    Dim r As Range
    For Each r In Selection
        v = r.Value
        If v <> "" And IsNumeric(v) Then
            If v >= 0 And v <= 0.005 Then
                r.ClearContents
                r.Value = "<1%"
            End If
        End If
    Next r
End Sub

Upvotes: 0

Related Questions