+ Reply to Thread
Results 1 to 4 of 4

Scoring Based on Cell Color

  1. #1
    Registered User
    Join Date
    01-08-2020
    Location
    Denver
    MS-Off Ver
    Office 365 64-Bit
    Posts
    2

    Scoring Based on Cell Color

    So I hit a road block on a large spreadsheet (300+ mb) that I've been working on.

    I've used conditional formatting to color code cells in 15-20 columns. I want to create a formula that will generate a score for that row, based on values that I assign to different colors. Some of the scores for the same color would vary based on the column. For example, a blue cell might be worth 5 in column M but 3 in column BC.

    Here's an example of my scoring (using random columns and scores for illustrative purposes):
    Column A would be excluded completely
    Blue cells (except column M) = 3
    Blue cells in column M = 6
    Green cells (except column M) = 4
    Green cells in column M = 10
    Yellow cells (except column M) = 2
    Yellow cells in column M = 4
    Orange cells (except column M) = 1
    Orange cells in column M = 2
    I would then have a cell at the beginning of the row that would tally up those scores and would be color-coded itself, using conditional formatting.

    Can anyone help point me in the right direction?

    Thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Scoring Based on Cell Color

    Hey Schiffy, would a small lookup table work for you?

    Edit: Hopefully you could use the same formulas that you put in the CF manager to choose the color from the table. If you HAVE to detect the color of the cell itself then I believe you'll need VBA.
    See this:- https://www.excelforum.com/excel-gen...or-format.html
    Attached Files Attached Files
    Last edited by Beamernsw; 01-09-2020 at 04:45 AM.

  3. #3
    Registered User
    Join Date
    01-08-2020
    Location
    Denver
    MS-Off Ver
    Office 365 64-Bit
    Posts
    2

    Re: Scoring Based on Cell Color

    Thanks for the reply Beamernsw. Unfortunately a lookup table won't work because it would end up being way to complex. Currently I have 30+ columns that have 1-4 CF formulas in each (about 80 CFs total). I still need to add another few columns with CF, which will probably bring the total CF count to around 100.

    I was hoping to find an easier solution than VB, as my VB coding knowledge all comes from examples I find online.

  4. #4
    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,401

    Re: Scoring Based on Cell Color

    Actually, a relatively complex lookup table would be less problematic, in the long term, than all these CF rules you are building. Excessive CF in a workbook will, in time, slow your workbook down to a crawl. I'd be interested to see a sample, as I am sure there are infinitely better ways of doing what you are trying to achieve.
    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.

+ 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. Change cell format (cell color+font color) based on color of another cell
    By Dedaluss in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2017, 03:27 AM
  2. Macro to change cell text color based on darkness of cell fill color
    By cincyshirm61 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2014, 12:11 PM
  3. [SOLVED] Color-format a cell which contains data in a column based on the color of another cell
    By Nit3hawK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2013, 05:25 PM
  4. VBA / Macro help needed to change the barchart color based on Cell Color
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2012, 09:15 AM
  5. [SOLVED] Gradual scoring based on value in a cell
    By Sjand in forum Excel General
    Replies: 4
    Last Post: 05-17-2012, 12:52 PM
  6. Replies: 5
    Last Post: 05-10-2012, 10:49 PM

Tags for this Thread

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