+ Reply to Thread
Results 1 to 7 of 7

format cells if they contain certain words

  1. #1
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    438

    format cells if they contain certain words

    Hi, if this range B21:E23 contains these words:

    Len
    wid
    Hgt
    Wgt
    User1
    User2
    User3
    #VALORE!

    the text must be colored green.
    With formatting or with a macro?
    I have excel 2007.
    Thanks you
    xam

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: format cells if they contain certain words

    you can use conditional formatting

    put you list in a different area or sheet

    and then use a countif()

    Not sure if countif() is available in version 2007 - quite old now
    Attached Files Attached Files
    Last edited by etaf; 11-21-2020 at 04:32 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: format cells if they contain certain words

    Put this in conditional formatting:

    =OR(A1="Len",A1="wid",A1="Hgt",A1="Wgt",A1="User1",A1="User2", A1="User3", A1="#VALORE!")
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    438

    Re: format cells if they contain certain words

    Hi belinda and etaf your formula work.
    This #VALORE! not is a word but a result of formula, in english = #VALUE!
    This #VALORE! not change color

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: format cells if they contain certain words

    works ok with the text, amd value error in the cell
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    438

    Re: format cells if they contain certain words

    Hi etaf,
    because not work in B21:E23 ?
    Attached Files Attached Files

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: format cells if they contain certain words

    conditional formatting is looking at different cells ?
    also the table has #value and the lookup #value!

    changed the reference to refer to the range B21 to E23
    Attached Files Attached Files

+ 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. How to concatenate to words in the same format?
    By reimar_rem in forum Excel General
    Replies: 2
    Last Post: 02-11-2018, 08:05 AM
  2. Format Cells with Duplicate words within a single cell
    By ocannon1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2017, 11:38 AM
  3. How to format numbers into words?
    By tonylyx in forum Excel General
    Replies: 7
    Last Post: 04-09-2014, 03:36 PM
  4. if cells contain "words" refer to other worksheet and replace the words
    By adehond in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 11:22 AM
  5. Replies: 0
    Last Post: 04-29-2013, 03:13 PM
  6. Replies: 1
    Last Post: 10-12-2012, 08:28 AM
  7. Delete words with certain format
    By sunandm00n in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2007, 12:05 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