Conditional Format Question

[boonetrailboy]

Hi,

I have a data table similar to the following:

9.1% 45.0%
4.0% 100.0%
99.0% 96.0%
75.0% 98.0%
55.0% 100.0%

I would like to mask the cells that are below 5% and above 95% to show <5% and >95% respectively.

These are large tables and not practical to manually edit. Any suggestions?

Thanks in advance,
John

 

[bbott]

So if a cell is, for example, 4.0%, you want to change the value of the cell to <5%?

 

[boonetrailboy]

Yes. Also change 99.0% to > 95%.

 

[bbott]

You can't replace with formulas in that way. Formulas can only change the value of the cell that contains the formula. I believe you'll need to use VBA. Are you open to a VBA solution?

 

[boonetrailboy]

Absolutely - no issues here with a VBA solution

 

[bbott]

Try this:

Sub FiveAndNinetyFive()

    Dim r As Range
    Dim s As Range
    
    Application.ScreenUpdating = False
    
    Set r = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count, _
        ActiveSheet.UsedRange.Columns.Count))
    
    For Each s In r
        If s < 0.05 Then
            s.Value = "<5%"
        ElseIf s > 0.95 Then
            s.Value = ">95%"
        End If
    Next s
    
    Application.ScreenUpdating = True
    
End Sub

If you have thousands of lines, it may take a bit. I would test it on a smaller sample.

 

[boonetrailboy]

Code worked like a charm - I appreciate the assist!

Thanks again,
John

 

[bbott]

Code worked like a charm - I appreciate the assist!

Thanks again,
John

You're welcome.