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?
Thanks!
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?
Code:
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