+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting, based on the adjacent cell to the left? Is this possible?

  1. #1
    Registered User
    Join Date
    10-15-2007
    Posts
    10

    Question Conditional Formatting, based on the adjacent cell to the left? Is this possible?

    Hi All,

    I've been scratching my head on this all morning!


    How would I make a cell in column J highlight yellow, if the adjacent cell in column I contains the word "SENT" ?


    Thanks so much,
    CG

  2. #2
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Conditional Formatting, based on the adjacent cell to the left? Is this possible?

    Hi

    follow this

    select cell in column J go to home--- conditional formatting option ---new rule ---use formula to determine which cell to format -- in type box write --IF(I8="SENT",1,0) -- use format in format tab ---ok

    this will solve your problem

    later you can select cell which conditionally formatted & go to format painter under home tab , & paste that formatting on required cells
    Click on * below if you find this helpful

    Thanks,
    A

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional Formatting, based on the adjacent cell to the left? Is this possible?

    Highlight the cells in column J that you want this to apply to - assume this is J2 to J100, with J2 as the active cell. Then click on Conditional Formatting | New Rule | Use a formula ...

    Put this formula in the dialogue box that opens:

    =ISNUMBER(SEARCH("sent",$I2))

    Click on the Format button, then the Fill tab and choose your colour. OK your way out. Then Excel will adjust the cell references to suit the other selected cells.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    10-15-2007
    Posts
    10

    Re: Conditional Formatting, based on the adjacent cell to the left? Is this possible?

    -- Accidental post; please see below reply instead.
    Last edited by chatguy; 08-19-2013 at 03:29 PM.

  5. #5
    Registered User
    Join Date
    10-15-2007
    Posts
    10

    Question Re: Conditional Formatting, based on the adjacent cell to the left? Is this possible?

    Hi Amy & Pete + Others,

    Apologies, I should have given more information. It would need to contain the word 'SENT' "as part of" the cell. (e.g.: "SENT 8/15/2013", or "SENT 7/29/13")

    So if Cell I6 contained the word 'SENT' as part of the cell, it would then highlight cell J6 in yellow (or cell I99 would affect J99, etc).

    Unfortunately the above didn't work -- I did try the above 2 solutions (and thanks for help so far, btw!) where it seemed to incorrectly color the "J-column", when the "I-Column" didn't contain that word -- and didn't color the "J-column" cells in when the "I-Column" did contain that word.

    Any ideas that might be able to help get it that far in Excel 2010?
    Thx again,
    CG
    Last edited by chatguy; 08-19-2013 at 03:14 PM. Reason: Clarification

  6. #6
    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 the adjacent cell to the left? Is this possible?

    @ amy, just fyi, you dont need...IF(I8="SENT",1,0), CF works on TRUE/FALSE, so all you need is =I8="SENT"

    @ chatguy, Pete's formula should have worked. Make sure you have the ranges set properly?

    maybe upload a sample workbook?
    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

+ 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. Conditional formatting on adjacent cell (Upper lower left right)
    By branred in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-05-2013, 12:52 AM
  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 values
    By dberliner in forum Excel General
    Replies: 4
    Last Post: 07-28-2011, 12:42 PM
  4. Conditional Formatting Based on Adjacent Cell
    By Yourgurlwantsme in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2010, 05:31 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

Tags for this Thread

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