VBA and Macros for 2007 R1C1 problem

sugrue

New Member
Joined
Feb 7, 2008
Messages
25
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
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.
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
    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:

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
 
Last edited:
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
 
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.
 
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
 

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top