Results 1 to 6 of 6

Conditional Formatting Referencing Different Worksheets

This is a discussion on Conditional Formatting Referencing Different Worksheets within the Excel Questions forums, part of the Question Forums category; Hi: I am currently using VLOOKUP with cells in the range $A$10:$D$131. I need the cells to return BLUE if ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    3

    Default Conditional Formatting Referencing Different Worksheets

    Hi:

    I am currently using VLOOKUP with cells in the range $A$10:$D$131. I need the cells to return BLUE if they are from A10:A47, GREEN if they are from A49:86, and RED if they are from A108:120. I tried Conditional Formatting using the menu, but it said I couldn't reference other worksheets (plus I wasn't sure if I had the formula correct for referencing text). I'm not real familiar with VB so if an explanation requires that, it needs to be thorough and detailed....

    Thanks for your help!
    Jeff

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,109

    Default Re: Conditional Formatting Referencing Different Worksheets

    WELCOME TO THE BOARD!

    In order to reference ranges found on other worksheets in Conditional Formatting, you need to name your range and reference it that way.

    To name a range, highlight the range, then from the Insert drop down menu, select Name, then select Define. Then give your range a name, like "MyRange".

    Then, to reference this range, instead of using something like A1:A100, simply use MyRange.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    3

    Default Re: Conditional Formatting Referencing Different Worksheets

    I still don't have it quite right. For the conditional format I have:

    =(A21=Data1) ...make font blue
    =(A21=Data2) ...make font red

    ...Data1 and Data2 are the names of my cell ranges ($A$10:$A$47). This only works though if A21=the first cell in Data1. What do I use if I want this to be true if A21= ANY cell in Data1?

    Thanks,
    Jeff

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,109

    Default Re: Conditional Formatting Referencing Different Worksheets

    Try the following conditional formatting:

    If you're range is A10:D131,

    Then in conditional formatting block 1, select
    Formula is: =MATCH(F10,$A$10:$A$131,FALSE)>=99
    Font color: Red

    In conditional formatting block 2, select
    Formula is: =MATCH(F10,$A$10:$A$131,FALSE)>=40
    Font color: Green

    In conditional formatting block 1, select
    Formula is: =MATCH(F10,$A$10:$A$131,FALSE)>=1
    Font color: Blue

    MATCH returns the array position. Since you are starting at Row 10, Row 10 marks the first position. So the difference between the row number and position number is 9. That is where the values come form (108-9=99, 49-9=40, 10-9=1)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,109

    Default Re: Conditional Formatting Referencing Different Worksheets

    I'm sorry, I forgot that your range was on a different page. If you have named the range "MyRange", then in all three match formulas, change:

    $A$10:$A$131

    to

    MyRange


    So the first one would look like:
    =MATCH(F10,MyRange,FALSE)>=99
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    New Member
    Join Date
    May 2003
    Posts
    3

    Default Re: Conditional Formatting Referencing Different Worksheets

    That worked well. Thanks for the help!

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