I recently wrote a piece of code that detects (but doesn't select) a Range which must be conditional formatted and then calls a subroutine that does the job.
' Format
For Each ws In Results.Sheets
Format_em_all ws.Range(ws.Cells(15, 2), ws.Cells(15 + UBound(FreqToCompare), WrittenCells))
Next ws
Where ws is declared as Worksheet, Results as Workbook, FreqToCompare is an array with no possibility of being empty and WrittenCellsis an Integer not = 0.
The Subroutine called:
Sub Format_em_all(RangeToFormat As Range)
Select Case RangeToFormat.Parent.Name
Case "lol"
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=2.9"
RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=2.00001", Formula2:="=2.9"
RangeToFormat.FormatConditions(2).Font.ColorIndex = 3
Case "rofl"
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:="=-4", Formula2:="=4"
RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
End Select
End Sub
I executed the code on my computer with Excel 2013 and all went smooth. 10 mins ago comes a colleague and says that my macro doesn't work... we go togheter to check on his computer with Excel 2010... and yeah, it doesn't work. It gets stuck at the first line after Case "lol" with a "Runtime error "5" - Invalid procedure call or argument". I tried to ? RangeToFormat.Address and it shows the correct range address... What is wrong with it?
Well the credit for this answer all goes to Axel Richter:
Sub Format_em_all(RangeToFormat As Range)
Select Case RangeToFormat.Parent.Name
Case "lol"
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=2.9
RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=2.00001, Formula2:=2.9
RangeToFormat.FormatConditions(2).Font.ColorIndex = 3
Case "rofl"
RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:=-4, Formula2:=4
RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
End Select
End Sub
As you can see, the formulas are not formulas anymore, but integers! That works flawlessly
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다