+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting help

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Conditional formatting help

    Hello, I have the following issue where I can't wrap my head around.
    I have a column which has text and I have to find if there's something which has a word which shouldn't be there.

    So I have decided to use Conditional Formatting and use the option Format only cells that contain: -> Specific Text -> Not Containing
    I can put 1 word in the section, but I have to have 10 words for it.

    Currently I haven't found any solution which works for me: tried something like =OR("RED";"YELLOW";"ORANGE";"BLUE") , but unfortunately it doesn't work.
    Any ideas guys?
    Last edited by flyby99; 03-21-2018 at 04:22 AM.

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Conditional formatting help

    Something like this:
    Please Login or Register  to view this content.
    Lots on this forum. . .

    Pete

  3. #3
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    Think I have found this also Pete, but if I have to use range then it would be like =OR(A1:A100="A";A1:A100="B";A1:A100="C";A1:A100="D"), because if I do it like this it doesn't work really.
    See, attachments.
    Attached Images Attached Images

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional formatting help

    Assuming that you want to Conditionally Format cells D2:D1000 that contain any of the words A, B, C, or D, you would accomplish that like this:

    Highlight D2:D1000 > Conditional Formatting > Use a formula
    =OR(D2="A";D2="B";D2="C";D2="D")
    Format > Fill color of your choice > OK > OK

    Note that this will only work if there is nothing else in the cell.
    If, for example, you wanted to CF a cell with this in it "123A456" (since it contains an "A" in the string), then you can use a formula like this:
    =OR(D2="*A*";D2="*B*";D2="*C*";D2="*D*")

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional formatting help

    Note that with Conditional Formatting, you only use (in the formula) the top-left most cell in the range that you highlight.

    For example, if you want to conditionally format A20:C100 for every cell that says flyby99, you would highlight A20:C100 and use this formula:
    =A20="flyby99"

  6. #6
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    I really appreciate your help, but hit me with a hammer on the head, it just doesn't work.
    Range is D2=D1000, "Applies to =$D$2:$D$1000)
    Formula is
    =OR(D2="*RIX*";D2="*AMS*";D2="*SVO*";D2="*MUC*")

    It doesn't work whether I just use formula and enter this formula or use cell value does not contain specific text and enter this formula

    [edit] checking your previous post now 63falcondude
    [edit2] understood, but still nothing on my part, probably missing something simple, as usual

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional formatting help

    Whoops, that was silly of me. An OR statement doesn't take wildcards like that.

    You'll be best off attaching a sample workbook for us to test on.

    Make sure there is just enough data to demonstrate your need.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"
    Last edited by 63falcondude; 03-19-2018 at 03:30 PM.

  8. #8
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    Okay,
    There's A column and it starts with one of the name on the D column. RIX, AMS, SVO etc.

    And I want to get a highlight up in the column if there's something which doesn't match the D column. For example NYC.

    Soon I'll be banging my head on the desk
    Thanks!
    Attached Files Attached Files

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional formatting help

    Ooh, that's not what I thought you were asking at all.

    Okay, try this:

    Highlight A2:A227 (or however far you want to go) > Conditional Formatting > New Rule > Use a formula
    =COUNTIF(D:D,LEFT(A2,3))=0
    Format > Fill color of your choice > OK > OK

  10. #10
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    I took out the sensitive data out of the test.xlsx so columns have changed, think I confused you by accident, sorry. Because this formula doesn't seem the right one, excel doesn't let me to apply it also.
    basically, I need to highlight a row from column A, which doesn't have any text which is in column D. On the test.xlsx file

  11. #11
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    63falcondude youre the man, here's +rep and a beer if youre in Riga sometime, thanks!

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional formatting help

    Happy to help, thanks for the rep!

    You may have to use semicolons instead of commas due to your regional settings.

    That would be =COUNTIF(D:D;LEFT(A2;3))=0

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Last edited by 63falcondude; 03-20-2018 at 08:17 AM.

  13. #13
    Registered User
    Join Date
    09-11-2014
    Location
    Riga
    MS-Off Ver
    2007
    Posts
    36

    Re: Conditional formatting help

    Will do, and yes, had to use semicolons

+ 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