Conditional Format Question

boonetrailboy

New Member
Joined
Mar 14, 2007
Messages
4
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
So if a cell is, for example, 4.0%, you want to change the value of the cell to <5%?
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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

New Member
Joined
Mar 14, 2007
Messages
4

ADVERTISEMENT

Absolutely - no issues here with a VBA solution
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Try this:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,311
Members
409,862
Latest member
lbisacca
Top