Please Help Me Select Cells

[Rekd]

I've got a cheesy macro that goes row by row and sets conditional formatting to color the highest value in red and the lowest value in blue. It was made by recording and modifying. It works well but the user inputs are, well, n00bishly cumbersome.

How can I change the macro to use the Application.Inputbox, Type:=8 so I can select the cell range I want instead of typing the columns/rows into regular input boxes?

Sub HiLoAll()

Dim cntRows As Long, R As Integer, f As String, l As String
f = InputBox("First Column?", , "T") 'Input left side of matrix
l = InputBox("Last Column?", , "X") ' Input right side of matrix
fr = InputBox("Start at What Row?", , "2") ' Input top of matrix
LR = InputBox("End at What Row? (Escape for all used rows)", , "10") ' Input bottom of matrix

If LR = "" Then
    cntRows = Range("B" & Rows.Count).End(xlUp).Row ' Set bottom of matrix
    Else: cntRows = LR
End If
For i = fr To cntRows ' Loop
    Range(f & i & ":" & l & i).Select ' Select row
    Selection.FormatConditions.Delete ' Delete existing formats
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=MIN($" & f & "$" & i & ":$" & l & "$" & i & ")" ' Min formula
    With Selection.FormatConditions(1).Font ' Set format
        .ColorIndex = 5
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=MAX($" & f & "$" & i & ":$" & l & "$" & i & ")" ' Max formula
    With Selection.FormatConditions(2).Font ' Set format
        .ColorIndex = 3
    End With
Next
    Range(f & fr).Select
End Sub

Thanks!

 

[AlphaFrog]

Try something like this...

Sub HiLoAll()
   
    Dim rng As Range
    
    On Error Resume Next                                                    ' Ignore errors
    Application.DisplayAlerts = False                                       ' Suppress alert messages
        Set rng = Application.InputBox("Pease sleect the range.", "Select Range", Type:=8)
    Application.DisplayAlerts = True                                        ' Resume alert messages
    On Error GoTo 0                                                         ' Resume error checking

    If rng Is Nothing Then Exit Sub                                         ' User canceled
    
    For i = 1 To rng.Rows.Count                                             ' Loop

        With rng.Rows(i)
        
            .FormatConditions.Delete                                        ' Delete existing formats
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                    Formula1:="=MIN(" & rng.Rows(i).Address & ")"           ' Min formula
            .FormatConditions(1).Font.ColorIndex = 5                        ' Set format
            
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:="=MAX(" & rng.Rows(i).Address & ")"                   ' Max formula
            .FormatConditions(2).Font.ColorIndex = 3                        ' Set format
            
        End With
        
    Next i
    
    rng.Rows(1).Select
        
End Sub

 

[Rekd]

    For i = 1 To rng.Rows.Count

^^^ This!

Thank you. Works perfect.