VBA and Macros for 2007 R1C1 problem

[sugrue]

I am having trouble with the code for Ch 6, Page 133 - Mark Largest Row. It does not work as expected, and the results I get vary depending on which cell is active when I hit the "Highlight Max" button.

When I open the spreadhsheet, Cell B23 is active. When I run the code, cells A2.I13 get highlighted in Green. If I click on one of the highlighted cells and go to Manage Rules, the formula is =RB1048562=MAX(B1048562). If I make cell A2 active before hitting the button, I get a different result. Columns A,B,F,G,H,I are all green and C,D,E are partially green and the formula is now =RC7=MAX(C7).

Any ideas?

Jack

 

[sugrue]

95 views and no response - is this the right place for me to post questions and concerns about the code related to the book? This is not a big deal - I have moved on the the next chapter, but would like to know what is wrong with the code. I thought I might be able to figure it out on my own like I did for a few other typos that I found, but no luck.

Any help?

Jack

 

[MrExcel]

This chapter is on R1C1 style formulas. When the Formula1 is clearly R1C1 or clearly A1, Excel will handle the formula correctly. In Excel 2003, the formula was clearly an R1C1 formula. When using R1C1 formulas, the active cell does not matter.

Sub FindMinMax()
    ' 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

In Excel 2007, if you are not in compatibility mode, the formula =RC7=MIN(C7) can be interpreted as a valid A1 style formula, causing the code to fail.

To have the code work in 2007, no matter if they are in compatibility mode or not, use an A1 style formula and make sure that the formula works relative to the active cell. Or...explicitly select the correct active cell in the code:

Sub FindMinMax()
    ' Highlight row with highest revenue in Green
    ' Highlight row with lowest revenue in Yellow
    FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row
    Range("A2").Select
    With Range("A2:I" & FinalRow)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=MAX($G:$G)"
        .FormatConditions(1).Interior.ColorIndex = 4
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=MIN($G:$G)"
        .FormatConditions(2).Interior.ColorIndex = 6
    End With
End Sub

Bill

 

[sugrue]

Thanks Bill,

This now works perfectly if any cell in Row 2 is the active cell. However, it still does not work if any other cell is active.

I added:
Range("A2").Select

as the first statement within the With Range block and it works fine regardless of which cell is active before you click the button. Is this an acceptable solution, or is there a better way to insure it works regardless of which cell is active?

Thanks,
Jack

 

[MrExcel]

You definitely need the Range("A2").select in there somewhere. Although... I can't see the difference between where I had it...just before the With block, and having it in the With block.

 

[sugrue]

Sorry about that. I didn't even notice that you had that in your updated code. I only noticed the changes to the 2 FormatConditions.Add statements. Your updated code works great!

Thanks,
Jack