Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    conditional format question (2003)

    I was wanting to use conditional formating between 2 sheet in the same workbook, well excell does not let me. Is there a work around for this? What I am wanting to do is create a copy of the orginal sheet(sheet1). Now having 2 sheets[sheet1(orginal) & sheet2(copy of orginal)], as changes are made to sheet1 I want to format the cells in sheet1 that are different form sheet2 with background color & Text color. Can someone tell me where to get started?
    thanks,
    jackal

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

    Re: conditional format question (2003)

    Select Insert | Name | Define...
    In the 'Names in workbook' box, type Other.
    In the 'Refers to' box, type

    =INDIRECT("Sheet2!" & CELL("Address"))

    Click OK.
    Select a range of cells in Sheet1.
    Select Format | Conditional Formatting...
    Specify Cell Value Is in the first box
    Specify 'not equal to' in the second box.
    Enter =Other (without quotes) in the third box.
    Click Format... to specify a background pattern or whatever you like.
    Click OK.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional format question (2003)

    I have attached a file that does what you said and I am not sure if I have done it right or I have misled you on what I am wanting. In Sheet1, I thought the cells with the conditional formating were not supposed to be formatted unless they did not match the corresponding cells in Sheet2, which they all do right now so no conditional formating should occur?
    jackal

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

    Re: conditional format question (2003)

    I'm sorry, I didn't test sufficiently; the defined name always refers to the active cell, so the conditional formatting won't work correctly. Instead, you can use conditional formatting with 'Formula Is' and formula

    =NOT(A1=INDIRECT("Sheet2!" & CELL("Address",A1)))

    See attached version.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional format question (2003)

    Hans,
    I must thank you once again...THANKS.
    This is perfect ! ! ! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Jackal

Posting Permissions

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