Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Shade cell in one sheet based on another sheet (Excel xp)

    I have 2 worksheets in a workbook. If there is an "s" in B255 of Sheet1, I need to shade D25 in Sheet2 yellow. Can I do this in a formula? I don't really want to write code because each instance is unique and it would be a lot of code. If i could put a formula in the cell such as =IF(Sheet1!B255="s", then Sheet2!D25 shade it red) then it would be easier. Thank you for any help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    You can use conditional formatting to do this (Format menu/Conditional Formatting...). However, conditional formatting has a restriction that the formula used for the conditional formatting can not reference cells on another sheet. Therefore, you would have to enter the formula =Sheet1!B255 into a cell somewhere on Sheet2 (I used cell E1 in the attached workbook). Then you can use the conditionl formatting formula for cell D25:

    <pre>=E1="s"
    </pre>

    Attached Files Attached Files
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    Thank you sooooo much. This worked perfectly, you r great!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    If you name the range, it can be anywhere in the workbook, even outside the workbook (linked name).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    That's what I thought. T tried it on my system and could not get it to work. I must be doing something wrong.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    I am confused. What were you trying?
    I can use named cells in the "formula is" and the "cell value is" portions, even if the named cell is in another sheet. with no problems.

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    That is what I tried and could not get to work. I just tried it again, and it does work. I don't know what I was doing wrong.
    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shade cell in one sheet based on another sheet (Excel xp)

    Sometimes excel likes to confuse you (if you don't type everything perfectly) and will convert your formula to a text string rather than a formula. Also if there are mistakes in it, it does not always tell you.

    Steve

Posting Permissions

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