怎么有错啊Sub GetMax()
Range.Find(Application.WorksheetFunction.Max(Range("D1:D24"))).Activate
ActiveSheet.Cells(1, 1).Interior.ColorIndex = 3
End Sub
Range.Find(Application.WorksheetFunction.Max(Range("D1:D24"))).Activate
ActiveSheet.Cells(1, 1).Interior.ColorIndex = 3
End Sub
Range("D1:D24").FormatConditions.Delete
Range("D1:D24").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MAX($D$1:$D$24)"
Range("D1:D24").FormatConditions(1).Font.ColorIndex = 3
Range("D1:D24").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MIN($D$1:$D$24)"
Range("D1:D24").FormatConditions(2).Font.ColorIndex = 6
Range("D1:D24").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MAX($D$1:$D$24)"
Range("D1:D24").FormatConditions(1).Interior.ColorIndex = 3
Range("D1:D24").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MIN($D$1:$D$24)"
Range("D1:D24").FormatConditions(2).Interior.ColorIndex = 6不错呀!
Range("A4").Select '最大值单元'
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A1").Select
Selection.Insert Shift:=xlDown
然后把格式复制到其它所有需要的列就可以了。
Sub Main()
Dim i&, strCh$
For i = 1 To 150 '对第1列到150列加条件格式
If (i < 27) Then
strCh = Chr$(64 + i)
Else
strCh = Chr$(65 + (i - 1) \ 26) & Chr$(65 + (i - 1) Mod 26)
End If
strCh = strCh & "1:" & strCh & "24"
Range(strCh).FormatConditions.Delete
Range(strCh).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MAX($D$1:$D$24)"
Range(strCh).FormatConditions(1).Font.ColorIndex = 3
Range(strCh).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MIN($D$1:$D$24)"
Range(strCh).FormatConditions(2).Font.ColorIndex = 6
Next
End Sub
Sub Main()
Dim i&, strCh$, strRange$
For i = 1 To 150 '对前150列加条件格式
If (i < 27) Then
strCh = Chr$(64 + i)
Else
strCh = Chr$(65 + (i - 1) \ 26) & Chr$(65 + (i - 1) Mod 26)
End If
strRange = strCh & "1:" & strCh & "24"
Range(strRange).FormatConditions.Delete
Range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MAX($" & strCh & "$1:$" & strCh & "$24)"
Range(strRange).FormatConditions(1).Font.ColorIndex = 3
Range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MIN($" & strCh & "$1:$" & strCh & "$24)"
Range(strRange).FormatConditions(2).Font.ColorIndex = 6
Next
End Sub
格式-->条件格式在其中可进行详细设置,还是很不错嘀~~
Dim i&, strCh$, strRange$
For i = 1 To 150 '对前150列加条件格式
If (i < 27) Then ‘这个27是干嘛用的
strCh = Chr$(64 + i)’还有这个64
Else
strCh = Chr$(65 + (i - 1) \ 26) & Chr$(65 + (i - 1) Mod 26)‘26一样不明白
End If
strRange = strCh & "1:" & strCh & "24"’strRange用来做什么?
Range(strRange).FormatConditions.Delete
Range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MAX($" & strCh & "$1:$" & strCh & "$24)"
Range(strRange).FormatConditions(1).Font.ColorIndex = 3
Range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MIN($" & strCh & "$1:$" & strCh & "$24)"
Range(strRange).FormatConditions(2).Font.ColorIndex = 6
Next
End Sub
这根本不应该算“疑问”,你要不是明白,只要在这句前加个:
Debug.Print i, strCh
看一下输出结果、对照一下 Excel工作表 的表头,就应该明白啊!
“strRange用来做什么?”
你没看到后面紧接着使用了5次strRange吗?若不用这个变量,你就得把后面的Range(strRange)替换成Range(strCh & "1:" & strCh & "24")。 这样每次都要做相同的运算。
象我给的“150列”的代码中,你愿意让它做150×3 = 450次字符串运算,还是愿意让它做150×3×5 = 2250次字符串运算?