+ Reply to Thread
Results 1 to 4 of 4

Conditional Formating when row does not equal or contain reference cell's value

  1. #1
    Registered User
    Join Date
    12-27-2014
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2010
    Posts
    43

    Conditional Formating when row does not equal or contain reference cell's value

    All,
    Wife is going thru her credit card charges in order to sort them for the accountant.
    How do I create conditional formatting to allow her to see which entries she has not completed and to catch typing errors (I know she can do copy/paste, but she prefers to type).

    What I'm envisioning is a way to do conditional formatting so that C2 is Yellow when data is not matched and Green, when any of the values in D2:S2 are equal to C2.

    Should I just create a hidden column where I sum D2:S2 and compare it to C2 in conditional statement?
    I do want to catch mistakes where she accidentally types more then one entry per row.

    I've attached sample file with just few entries, vs. several hundred/thousand that she might have.
    Obviously, all I did in the C2:C2 cells is Fill Color to illustrate desired result.
    I listed 3 desired outcomes, but really, all that's needed is "proper entry"=Green and anything else (no fill or color) for anything else.

    Thank you for your time.

    Excel formatting Example.xlsx
    Last edited by mariomp; 01-22-2016 at 01:11 PM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Conditional Formating when row does not equal or contain reference cell's value

    Hi
    1st. formula Green
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2nd. formula Yellow
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3th. formula RED
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by José Augusto; 01-22-2016 at 01:24 PM. Reason: Replace ; by ,

  3. #3
    Registered User
    Join Date
    12-27-2014
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2010
    Posts
    43

    Re: Conditional Formating when row does not equal or contain reference cell's value

    Quote Originally Posted by José Augusto View Post
    Hi
    1st. formula Green
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2nd. formula Yellow
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3th. formula RED
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jose, thanks for the formulas.
    I will try them tonight.

  4. #4
    Registered User
    Join Date
    12-27-2014
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2010
    Posts
    43

    Re: Conditional Formating when row does not equal or contain reference cell's value

    Thank you José.
    Your formulas work exactly as expected.

+ 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 Formating - Copy/Paste/Relative Reference
    By prudential in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2015, 09:50 AM
  2. Replies: 4
    Last Post: 01-23-2014, 10:14 AM
  3. when is equal not equal - cell reference results to blank cell
    By riwiseuse in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2009, 09:44 AM
  4. Conditional Formating with reference to another cell
    By Spencinator in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2008, 05:56 AM
  5. Replies: 3
    Last Post: 03-30-2006, 10:02 AM
  6. Conditional formating-change colour when D30 is equal
    By smith.james0 in forum Excel General
    Replies: 2
    Last Post: 07-26-2005, 01:05 PM
  7. Replies: 2
    Last Post: 05-04-2005, 10:06 AM

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