I recorded a macro to conditionally format a row of numbers highlighting the minimum non-zero number in the row. That worked fine. I modified the vb code to do the same thing for a range of rows. I assigned the macro to ctrl-f. When I press ctrl-f, the macro runs and highlights the zeros rather than the minimum non-zero numbers. But, when I highlight the range, call up the conditional format and click OK, the conditional format does what I intended it to do. Can somebody help me? I have pasted the VB code below. Notice that I commented out the original macro that was recorded.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/18/2008 by rodk
'
' Keyboard Shortcut: Ctrl+f
Dim x As Integer
Dim sx As String
Dim forumla As String
For x = 1 To 2 Step 1
sx = CStr(x)
Range("a" & sx & ":f" & sx).Select
On Error Resume Next 'in case the range doesn't have a cf
Selection.FormatConditions.Delete
Formula = "=a" & sx & "=min(if($a$" & sx & ":$f$" & sx & "<>0," & _
"$a$" & sx & ":$f$" & sx & "))"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Formula
Selection.FormatConditions(1).Interior.ColorIndex = 6
Next x
'ORIGINAL RESULTS FROM THE MACRO I RECORDED
'Range("A1:F1").Select
'Selection.FormatConditions.Delete
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
' "=A1=MIN(IF($A$1:$F$1<>0,$A$1:$F$1))"
'Selection.FormatConditions(1).Interior.ColorIndex = 6
'Range("A2").Select
End Sub
Bookmarks