Results 1 to 9 of 9

Using conditional format and referencing another worksheet

This is a discussion on Using conditional format and referencing another worksheet within the Excel Questions forums, part of the Question Forums category; Hi, I use a worksheet with data in it and I want to use conditional formatting to highlight a cell ...

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    70

    Default Using conditional format and referencing another worksheet

    Hi,

    I use a worksheet with data in it and I want to use conditional formatting to highlight a cell if there is data in another worksheet within the same workbook.

    So, for example, Sheet A, column c is fully populated down to row 500. If I enter text in Sheet B, cell B1 I want Sheet A, cell C1 to have shading applied.

    I know this is possible as I did have it but someone deleted it from my sheet.

    Thanks in advance

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,553

    Default Re: Using conditional format and referencing another worksheet

    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    70

    Default Re: Using conditional format and referencing another worksheet

    Apologies, but I'm not following this. I used the link but it seems to be suggesting matches. The cell I want to reference to will not have the same data as the cell I want to format. Both cells have text and if cell B1 in Sheet B is blank then cell C1 in Sheet A will have no format. However, if cell B2 in Sheet B has text then cell C2 in Sheet A has a colour.

    I am using Excel 2007 if this helps.

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,553

    Default Re: Using conditional format and referencing another worksheet

    Try the Use a formula... option with the formula

    =Sheet2!B2<>""
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Posts
    70

    Default Re: Using conditional format and referencing another worksheet

    Cheers but I get the message 'You cannot use references to other worksheets or workbooks for conditional formatting crieteria.'

    The annoying thing is I recently had this until it got wiped and I recall using the "formula" criteria in Formatting.

  6. #6
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,553

    Default Re: Using conditional format and referencing another worksheet

    Sorry, I'm getting confused. That would work in 2010. In earlier versions you need to use named ranges. So if you named Sheet2!B2 as Fred you could use

    =Fred<>""
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644

    Default Re: Using conditional format and referencing another worksheet

    With 2007 and earlier, you need to assign a name to Sheet2!B2
    e.g.
    Name: Sheet2_B2, or whatever you want
    Refers to:
    Code:
    =Sheet2!$B$2
    Then you can refer to this from anywhere in the workbook
    e.g.
    Sheet1 Cell A1
    Conditional Formatting > Formula is
    Code:
    =Sheet2_B2=5
    Forrmat "Yellow"

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Posts
    70

    Default Re: Using conditional format and referencing another worksheet

    That highlights all cells. I've sort of got around it by using an if statement in Sheet A worksheet which makes another cell "1" if there is text in a specific cell in Sheet B. Then I can reference that. It seems to work.

    Thanks for your help thoough.

  9. #9
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644

    Default Re: Using conditional format and referencing another worksheet

    That highlights all cells
    ... only if you selected all the cells and applied the C/F to them!

    A word of warning 2010 can reference different sheets without using names, but I know several large companies still using 2003 with 2007 compatibility.
    Probably safer to use names in 2010 for a few years yet!

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