Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting conditional format references in macro (Excel 97/2k)

    I have a problem with formulae in conditional formats when I sort the data in a sheet. The conditional format macro is along the lines of Formula1:="=MIN(" & r1.Address() & ":" & r1.Offset(0, 1).Address() & "," & r1.Offset(0, 3).Address() & ":" & r1.Offset(0, No_of_Columns).Address() & ")" which results in addresses having the format $A$2 which do not get changed when the data is sorted, resulting in the conditional format pointing to a different row after sorting. Is there some way that i can alter the macro to get cell references that follow the new position after the sort?

    TIA
    Alan.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Converting conditional format references in macro (Excel 97/2k)

    The Address function has several optional arguments. the first two are RowAbsolute and ColumnAbsolute. Both are Booleans and their default value is True, meaning that the address returned will be absolute, such as $A$2. If you use r1.Address(False, False), you will get a relative address, such as A2. If desired, you can mix: r1.Address(False, True) will return an address like A$2.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting conditional format references in macro (Excel 97/2k)

    Thanks Hans, that did it. I had been looking in the help under Offset(), but had not thought to look under Address(). Once you pointed it out, it became plain when looking at the help.
    Thanks
    Alan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •