Look at the code to create this conditional format.The formula to find the highest value in Column G,expressed in R1C1-style, is MAX(C7).This can be resolved two ways: either as an A1-style formula or as an R1C1-style formula. Remember— Microsoft started with the R1C1 style and offered the A1 style only to be compatible with Lotus 1-2-3.This is undocu-mented,but clearly Excel first tries to resolve the formula using R1C1-style formatting and the following code works perfectly.

Sub FindMinMax()

1 Highlight row with highest revenue in Green ' Highlight row with lowest revenue in Yellow FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row With Range("A2:I" & FinalRow) .FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, Formula1:="=RC7=MAX(C7)" .FormatConditions(1).Interior.ColorIndex = 4

.FormatConditions.Add Type:=xlExpression, Formula1:="=RC7=MIN(C7)" .FormatConditions(2).Interior.ColorIndex = 6 End With End Sub

0 0

Post a comment