Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation Question (2003 sp2)

    Greetings!

    Here is the situation. I have a file that contains data in col a (primary validation listing). On another tab, I have 2000 lines of data. col C contains information I would like to check against the primary list for a match, if there is a match, I need to change the color of the cell to blue. What is the most effecient method?


    Thanks,
    Brad

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

    Re: Data Validation Question (2003 sp2)

    Select the data in column C.
    Note carefully which cell is the active cell - you can see this in the address box on the left hand side of the formula bar. For this post, I'll assume it is C1.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    Enter the following formula in the box next to it, substituting the appropriate name and range:

    =COUNTIF('Other Sheet'!$A$1:$A$100,C1)

    Other Sheet is the name of the sheet containing the validation list, and A1:A100 is the address of this list.
    C1 is the currently active cell.
    Click Format... and specify the formatting you want, in the Font and/or Pattern tab.
    Click OK twice.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Question (2003 sp2)

    Will do. Guess I did not realize you could do that in a conditional formatting section.


    I'll try it and let you know...

    BRad

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Question (2003 sp2)

    Hans,

    Receive the following error message:

    You may not use references to other worksheets or workbooks for Conditional Formatting criteria.


    Brad

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

    Re: Data Validation Question (2003 sp2)

    Oops, should have known that. Sorry. You can use a named range:

    Select Insert | Name | Define...
    Enter a name, e.g. MyList.
    Select or enter the validation list in the Refers to box:

    ='Other Sheet'!$A$1:$A$100

    Click OK.
    Select the data in column C again.
    Repeat the steps from my previous reply, but change the formula to

    =COUNTIF(MyList,C1)

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Question (2003 sp2)

    That did the trick.


    Thanks,
    Brad

Posting Permissions

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