Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting (Excel 2003)

    I have a cell that does a complicated lookup to return an account value. Another cell has a 4-digit branch number. I need to create a conditional format test that says "if the value in the complicated lookup cell is less than 500000 and the value in the branch cell is not in a certain range (7000-8000) then format it so I can spot the error. This is to ensure that manufacturing expense accounts match with manufacturing branch numbers and overhead with overhead. So a 499999 account with a 6999 or less branch or an 7999 or higher branch would create a red background or something like that. Is that possible? I'm sorta stuck in asking the test to test the value of the cell itself. Thanks!

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

    Re: Conditional formatting (Excel 2003)

    Let's say that the cell with the complicated lookup formula is B4, and the cell with the branch number is C4.

    Select the cell that you want to format.
    Select Format | Conditional Formatting...
    Select "Formula Is" from the first dropdown.
    Enter the following formula in the box next to it:

    =AND(B4<500000,OR(C4<7000,C4>7999))

    Click Format...
    Specify the desired formatting.
    OK your way out of the dialogs.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2003)

    I didn't know you could reference other cells like that when using conditional formatting. I should have asked here long ago! This is going to be fun.
    Michael Coleman
    BOMI International

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2003)

    Thanks much! Works well. Someday, somebody will stump you guys, but it won't be me!

Posting Permissions

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