+ Reply to Thread
Results 1 to 5 of 5

Auto complete cells with range and colour

  1. #1
    Registered User
    Join Date
    01-20-2019
    Location
    Belfast
    MS-Off Ver
    2013
    Posts
    8

    Auto complete cells with range and colour

    Hi folks

    Having another crack at this one.. (see attachement)

    So i want a formula that when i key in a number in Cell A1, that it completes B2 and C2

    In B2, i want it to auto select from a number range, and give me text
    In C2, i want text colour and the text to be that colour

    so for example.. if i key in 15(A1), it looks to the table of ranges F1-F6 (this one being B11-20) and output in B2, and in C2 it will look to the names and colours in G2-G6 and output RED in this example.

    Thanks folks!
    Nick
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Auto complete cells with range and colour

    Nickpop - you were asked to update the title in your original thread, NOT start a new one. I have now closed the older thread in favour of this one.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-20-2019
    Location
    St. Louis
    MS-Off Ver
    Office365
    Posts
    3

    Re: Auto complete cells with range and colour

    Multiple ways to accomplish. One solution uses nested IF functions and VLOOKUP.
    Picture3.png

    Another solution skips looking up the ranges and uses an INDEX and SUMPRODUCT function to return the color corresponding with the value.
    Picture4.png

    Both solutions above use conditional formatting to set the text color.

    Here's a sample spreadsheet. Ranges.xlsx

  4. #4
    Registered User
    Join Date
    01-20-2019
    Location
    Belfast
    MS-Off Ver
    2013
    Posts
    8

    Re: Auto complete cells with range and colour

    Lovely jubbly!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Auto complete cells with range and colour

    Did you see and understand post #2?

+ 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. VLOOKUP: Auto-complete a column (48 cells long) from a dropdown box
    By hazemagz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2016, 11:51 PM
  2. Auto-complete limited to 121 cells?
    By twobob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2014, 11:17 PM
  3. [SOLVED] Auto complete from 1 sheet to another (can not repeat cells info)
    By ricdamiani in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 08-12-2013, 02:27 AM
  4. Replies: 4
    Last Post: 06-13-2012, 01:34 AM
  5. How do I auto complete cells?
    By sonars in forum Excel General
    Replies: 3
    Last Post: 04-24-2009, 06:01 AM
  6. Using database to auto complete a series of cells
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2008, 02:17 AM
  7. Replies: 3
    Last Post: 01-02-2007, 09:03 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