putting the range of selected text into an Excel formula

[Nathan Sharfi]

First, my apologies if this was posted here a year ago.

I'm making a macro that would help speed a Conditional Formatting task that
I have to do on my worksheets. the end goal is to select a range, and with
that range, highlight the first, second and third lowest times of that
selection. My guess is that $B$2:$B$10 (the range that I used to record the
macro) needs to be replaced with something else, and I found
Application.Selection as a possible tool that could paste the range that I
needed. However, it seems that Application.Selection is for VBA functions,
not Excel ones.

What do I need to put in?

Sub FastestHighlight()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=SMALL($B$2:$B$10,1)"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=SMALL($B$2:$B$10,2)"
Selection.FormatConditions(2).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=SMALL($B$2:$B$10,3)"
Selection.FormatConditions(3).Interior.ColorIndex = 15
End Sub

[Tom Ogilvy]

Your hard coded range should be changed to reflect the range you want
conditionally formatted - it is unclear how this is determined, but if you
want to select a range of cells and then run the code to conditionally
format them, then selection is the object you need.

However, to have conditional formatting work, your formula has to resolve to
True or False. Your formula does not and takes no consideration of the cell
that is being formatted. In otherwords, all cells would return the smallest
value in the range B2:B10. If this is zero, they would all consider this to
be false - otherwise they would all consider it to be true and the first
format conditon would be applied - but you can't furnish a formula to the is
equal to as far as I know. What you need to do is test if the value in the
cell being formatted matches the Smallest value in the range.

=B2=Small($B$2:$B$10,1)

By using relative references for the cell and absolute for the range, this
can be applied to a group of cells at once - apply the formula as if the top
cell in the range is being formatted and Excel will adjust the relative
reference to the other cells. You also want the Type to be an Expression
rather than what you had. The below code worked fine for me:

Sub FastestHighlight()
sFirst = Selection.Resize(1, 1).Address(False, False)
sRange = Selection.Address(True, True)
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sFirst & "=SMALL(" & sRange & ",1)"

With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 15

Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sFirst & "=SMALL(" & sRange & ",2)"
Selection.FormatConditions(2).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sFirst & "=SMALL(" & sRange & ",3)"

Selection.FormatConditions(3).Interior.ColorIndex = 15
End Sub

Regards,
Tom Ogilvy
MVP Excel

Nathan Sharfi wrote in message ...