+ Reply to Thread
Results 1 to 16 of 16

how to fill cells if next cells have specific text

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Question how to fill cells if next cells have specific text

    Hello. Can someone explain me how do I automatically make those cells (Title, Developer, Publisher, Japan) gray if Europe and America cells have Unreleased text?

    i.hizliresim DOT com/26P5g2.jpg
    Attached Images Attached Images

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Try Conditional Format formula in E9 down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    tbh I don't know what to do with that code

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Select the data cells in column E and then click on the Conditional Formatting icon. Choose formula and put the formula in the box. Choose Format and pick a grey.

  5. #5
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    worked for some cells and didn't work for some, idk why. Thank you TMS for helping anyways.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    Sure. Sorry for delay. I didn't expect further help.

    As you can see it randomly fills cells gray. I want to fill Japan cells if EU and USA are "unreleased".
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    That will be because the sample data starts in row 12 whereas the image has the data starting in row 12.

    Change the formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    Still random cells are coloured regardless of EU and USA check

    Untitled.jpg
    Last edited by matz47; 02-16-2019 at 07:28 PM.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Yes, and that's because you have selected the entire column, not just the data cells. See my original response in post #2.

    Try Conditional Format formula in E9 down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you change the formula from row 9 to row 12, you need to specify row 12 down instead of 9 down.

  11. #11
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    Okay, it works but works for only that cell lol. What about entire excel sheet? I could fill the only cell manually on my own anyways. I am looking for something to fill all of those cells that have same condition. For example 17, 23, 27th cells, they have same contidion.

    Edit: "...17, 23, 27th cells..." fixed numbers.

    Untitled.jpg
    Last edited by matz47; 02-16-2019 at 08:07 PM.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Not sure how to explain E9 down or E12 down differently. Select cells in column E from E12 down to the last cell in column E that has data in it. With that range of cells selected, click on the CF icon and put the formula in.

    https://support.office.com/en-us/art...b-f1951ff89d7f

  13. #13
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    Quote Originally Posted by TMS View Post
    Not sure how to explain E9 down or E12 down differently. Select cells in column E from E12 down to the last cell in column E that has data in it. With that range of cells selected, click on the CF icon and put the formula in.
    Oh, sorry. Finally figured out. It works but if I select other colums (From B12 to E2959), it won't work. I want the entire lines get filled with gray.

    Untitled.jpg

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    Change the column reference to be absolute...


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    02-12-2019
    Location
    Istanbul
    MS-Off Ver
    2007
    Posts
    8

    Re: how to fill cells if next cells have specific text

    Quote Originally Posted by TMS View Post
    Change the column reference to be absolute...


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Done. Thank you for dealing with me. I'm very new to excel. Cheers.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: how to fill cells if next cells have specific text

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Replies: 1
    Last Post: 09-06-2018, 03:31 AM
  2. [SOLVED] Counting cells with specific text excluding if date in specific cells
    By FraserMc97 in forum Excel General
    Replies: 2
    Last Post: 04-07-2017, 06:19 AM
  3. [SOLVED] Adding specific text to cells based on another cells data criteria
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-26-2015, 02:08 PM
  4. Replies: 1
    Last Post: 11-12-2014, 10:57 AM
  5. I would like A1 to auto fill with a specific tect if 3 other cells contain specific text
    By Mattyevs1000 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-09-2014, 03:35 PM
  6. Counting cells with colour fill, minus the cells with text
    By Crappy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-30-2013, 03:20 AM
  7. fill empty cells with text of cells above
    By Huug in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2012, 01:37 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