+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting - Looking for a NoMatch type command

  1. #1
    Registered User
    Join Date
    02-17-2018
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    8

    Conditional formatting - Looking for a NoMatch type command

    I am trying to perform conditional formatting on a cell to highlight when there is a mismatch in the cells

    Cells in col b have values like US123 or CA456...cells in col d have values like us456 or ca789

    Using the formula =MATCH("*"&LEFT(B10,2)&"*",D10,0) gives me the exact opposite of what I am looking for. This formula will highlight a cell in col d that starts with "us" where its sister cell (in col b) also starts with "us". What I want to do, is highlight col d if the first two characters in each cell do NOT match.

    I have tried =not(MATCH("*"&LEFT(B10,2)&"*",D10,0))...but basically nothing happens...no error...but no highlighting. I've been searching and can't find a "nomatch" type command, or a variable for match that would return something if cells don't match.

    Any insight would be appreciated.

    Thanks,
    Bill

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Conditional formatting - Looking for a NoMatch type command

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formatting - Looking for a NoMatch type command

    I think you were on the right track trying to use NOT.

    But try ISERROR instead.
    =ISERROR(MATCH(....))

  4. #4
    Registered User
    Join Date
    02-17-2018
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    8

    Re: Conditional formatting - Looking for a NoMatch type command

    That's the trick Jonmo1...thank you so much! This is the command I was looking for

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formatting - Looking for a NoMatch type command

    You’re welcome

+ 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 - How to use formula type???
    By eeps24 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-24-2017, 08:27 AM
  2. conditional formatting based on type of text
    By asmi in forum Excel General
    Replies: 2
    Last Post: 06-10-2016, 10:48 AM
  3. Conditional Formatting keeps wanting me to type apostrophes
    By taylorsm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-17-2016, 02:10 PM
  4. Choose the type of conditional formatting
    By Beginner Level in forum Excel General
    Replies: 3
    Last Post: 05-04-2012, 05:12 AM
  5. [SOLVED] Change font type in Conditional Formatting
    By Kyndsberg in forum Excel General
    Replies: 1
    Last Post: 01-23-2006, 05:20 PM
  6. Conditional Formatting type of operation
    By titushanke in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-13-2005, 03:25 AM
  7. Conditional formatting type question...
    By mightyhef in forum Excel General
    Replies: 0
    Last Post: 02-09-2005, 08:03 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