Page 1 of 2 12 LastLast
Results 1 to 10 of 16

highlight row / column with conditional formatting

This is a discussion on highlight row / column with conditional formatting within the Excel Questions forums, part of the Question Forums category; I have a workbook saved as Excel 97/2003 workbook*.xls (compatibility mode) that uses the following VBA and conditional formatting to ...

  1. #1
    Board Regular markstro's Avatar
    Join Date
    Sep 2008
    Location
    Vista Ca
    Posts
    106

    Default highlight row / column with conditional formatting

    I have a workbook saved as Excel 97/2003 workbook*.xls (compatibility mode) that uses the following VBA and conditional formatting to highlight the row/column the selected cell occupies.

    VBA
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = True

    Conditional Formatting formula
    =OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))

    This combination works just fine in the 97/2003 workbook, but not another saved as *.xlsm format.

    What changed between Excel 97-2003 and Excel 2007??
    I run a compatibility check and get an overlapping conditional formatting problem, not sure how to fix it or if it is what is effecting my formatting to work.

    I have used this formatting / code successfully on other workbooks saved as 97/2003 but have problems getting it to work in Excel 2007.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,043

    Default Re: highlight row / column with conditional formatting

    It works fine for me in an xlsm in Excel 2007, provided that macros are enabled.
    Microsoft MVP - Excel

  3. #3
    Board Regular markstro's Avatar
    Join Date
    Sep 2008
    Location
    Vista Ca
    Posts
    106

    Default Re: highlight row / column with conditional formatting

    I have verified my macros are enabled with the .xlsm workbook
    Where else can I look, probably something simple I don't know about.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,043

    Default Re: highlight row / column with conditional formatting

    What do you mean by "overlapping conditional formatting problem"?
    Microsoft MVP - Excel

  5. #5
    Board Regular markstro's Avatar
    Join Date
    Sep 2008
    Location
    Vista Ca
    Posts
    106

    Default Re: highlight row / column with conditional formatting

    From the compatibility checker results

    "Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all ot the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting" Location:'Scratch Pad', Conditional Formatting

    Scratch pad is the title of the worksheet

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,043

    Default Re: highlight row / column with conditional formatting

    That's explained here:

    http://office.microsoft.com/en-us/ex...onalformatting

    Did you click Find?
    Microsoft MVP - Excel

  7. #7
    Board Regular markstro's Avatar
    Join Date
    Sep 2008
    Location
    Vista Ca
    Posts
    106

    Default Re: highlight row / column with conditional formatting

    Yes I did, all it does is move to that worksheet and show you the cells affected.

    I am not sure if that problem is preventing the row/column highlighting from happening or not.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,043

    Default Re: highlight row / column with conditional formatting

    And what is the conditional formatting for those cells?
    Microsoft MVP - Excel

  9. #9
    Board Regular markstro's Avatar
    Join Date
    Sep 2008
    Location
    Vista Ca
    Posts
    106

    Default Re: highlight row / column with conditional formatting

    Cell fill color only

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,043

    Default Re: highlight row / column with conditional formatting

    And only one rule?
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com