+ Reply to Thread
Results 1 to 4 of 4

Conditionally formating a range of cells based on a formula

  1. #1
    Registered User
    Join Date
    11-04-2005
    Posts
    9

    Conditionally formating a range of cells based on a formula

    Hi all,

    I had a problem yesterday which I couldn't solve. I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.

    I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.

    Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:

    A: Person's name
    B: Value of 1 or 0

    The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"

    I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.

    Could someone please tell me:
    1) How would I get conditional formatting working over a range
    2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.

    Thanks so much for your help!

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditionally formating a range of cells based on a formula

    You need to use named ranges for conditional formatting from other sheet.

    See the example.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    11-04-2005
    Posts
    9

    Re: Conditionally formating a range of cells based on a formula

    Aha thanks so much Fotis! I didn't even know you could name ranges in Excel (I'm new...) but that makes complete sense. You've saved me loads of time.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditionally formating a range of cells based on a formula

    You are welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1