+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting -

  1. #1
    Registered User
    Join Date
    01-22-2019
    Location
    Kansas City, MO
    MS-Off Ver
    Version 14
    Posts
    4

    Conditional Formatting -

    I'm struggling with finding / writing a formula in Conditional Formatting that will Cell fill and Font color change when cell matches another cell. What I'm trying to do is when cell A has the same unique "value" as Cell B then I want the condition to kick in to make the cell Red with White font.

    A1 = 656238043229
    B1 = 656238043229

    BUT
    A2 = 4260167257235
    B2 = 717669750084

    So I want the condition to change both A1 and B1 to a Red Cell Fill and White Font; but when it review's A2 & B2 it ignores and changes nothing.

    I know how to get it to change when a "Specific" word is in question or in the quotes; but I do not have repeating values so it needs to be "wild".

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420

    Re: Conditional Formatting -

    Hi and welcome to the forum.

    Surely in A1 all you need is
    =$A1=$B1
    and format the colours you want and then apply the CF to the whole of column A&B

    It's somewhat confusing however since you mention use of a specific 'word' when in this simple examole there is no word. Nor indeed do I understand what you mean by repeating characters.

    This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-22-2019
    Location
    Kansas City, MO
    MS-Off Ver
    Version 14
    Posts
    4

    Re: Conditional Formatting -

    A B C
    1 Item Number UPC on Item System UPC
    2 J14703000001005 4260167257235 717669750084
    3 502053000439000 717669750084 4260167257235
    4 L26455000000000 Missing Blank <-- This one is NOT the same but the formula did NOT CF'd and Font
    5 L47371000001000 856011005448 0882030252518 <-- This was CF'd Red and Font turned white - even though not same "value"
    6 K45701000000000 845173003447 845173003447 <-- This is same on both item and System - Feed Error between systems. Cell B6 & C6 are Red CF and White Font.
    7 L44537000001000 042406564502 042406542166 <-- This one is NOT the same but the formula still CF'd and Font changed like B=C
    8 J48979000003000 Unknown 642388316573 <-- This one is NOT the same but the formula still CF'd and Font changed like B=C


    I know there is a way to put the document on here, but I've not had a chance to really look at this forum.

    Example however above is something I'm looking at. I'm taking UPCs from a item physically and bouncing it against the System. Sometimes the physical UPC is the same as system shown; this could clue me
    into a feed problem between 2 of our systems. I wanted Both Column B and C to cell Fill Color and Font change IF both Column B & C were the same.

    When I stated that specific word is used is b/c I've looked at other forums or excel help sites and they kept only stating the condition of "Format only cells that contain" and using a specific term to
    alter a cell's color fill/font style.

    Yes, the formula did work and didn't work. Please see comments against a few lines.

    If I try the =$B2=$C2 it gives the results shown above.

    IF I try to just do =$B=$C I get error:
    "The formula you typed contains an error." etc.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,597

    Re: Conditional Formatting -

    Hello Christiandeej and Welcome to Excel Forum.
    As to a conditional format formula to highlight duplicates in columns B and C of the same row (excluding blank cells) try: =AND($B2<>"",$C2<>"",$B2=$C2) applies to B2:C8
    As to uploading a sample spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  2. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 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