+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting based on text in adjacent cell

  1. #1
    Registered User
    Join Date
    02-16-2014
    Location
    canada
    MS-Off Ver
    excel 2011 MAC
    Posts
    2

    Conditional formatting based on text in adjacent cell

    Hi there, I am hoping someone can help me with my budget spreadsheet:

    A2:A5 contains different dollar amounts, a6 is the sum of those amounts

    In column B (B2:B5), i place a "p" beside the amount in column A once it has been paid.

    I want to:
    a. conditionally format the amounts in column a to turn green once I put the 'p' in the adjacent column
    b. conditionally format the sum in A6 to exclude amounts that have been paid, and only display the total amount of the unpaid lines.


    thank you!

    Laura

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting based on text in adjacent cell

    Hi and welcome to the forum

    1. highlight the range you want to apply the conditional formatting to (A2:A5)
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =$b2="p" format green

    For the 2nd part, you wont use Condtional formatting for that - CF if only for cosmetic purposes, it doesant actually calc anything or change the contents of a cell

    Instead, use this...
    =sumif($B$3:$B$5,"p",$A$2:$A$5)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-16-2014
    Location
    canada
    MS-Off Ver
    excel 2011 MAC
    Posts
    2

    Re: Conditional formatting based on text in adjacent cell

    Thank you!

    I put the sumif formula in A6, but its giving me the total of the green amounts. I want it to show the sum of the ungreen amounts... so a sumif formula that excludes the green (or "p") values

    Laura
    Last edited by lauralaura; 02-16-2014 at 05:18 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting Columns Based on Text from Adjacent Column
    By mejiamang in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2012, 10:41 PM
  2. Conditional Formatting based on Adjacent Cell
    By MacroPolo in forum Excel General
    Replies: 3
    Last Post: 06-23-2012, 01:41 AM
  3. Conditional Formatting Based on Adjacent Cell
    By Yourgurlwantsme in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2010, 05:31 PM
  4. [SOLVED] Conditional formatting of adjacent cells based on text in cell
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2006, 07:20 PM
  5. [SOLVED] How to do a conditional formatting based on an adjacent cell
    By Confused in forum Excel General
    Replies: 2
    Last Post: 01-10-2005, 06:06 PM

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