1

I'm still learning excel, and I'm trying to automate one of our products to make it simpler and faster. Basically, I want to reference a range of data from a set of cells, and color code it. We already color code a set of cells based on a time period green, yellow, or red.

Now what I need it to do is have it reference the cells, and change the color automatically. For example, if some of the cells are yellow, and some are red, I need it to color the reference cell in another sheet in the same workbook red. If some are green and some are red, I need it color code it red. Basically red overrides everything, yellow overrides green, and green by itself is fine.

Unfortunately, macros are disabled for work, so I can't go that route. Is there a formula I could use to accomplish this? Or is this something I'm unable to accomplishable with a formula? I've scoured the internet, and I can find plenty of info with macros, but I can't use macros for this.

Many thanks in advance!

Screenshot

PS - This is an example of the spreadsheet attached.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Nebu10us
  • 11
  • 2
  • 1
    Maybe explain the time period logic you already use to color the "source cells." You might be able to use conditional formatting based on that logic. Some sample data and screenshots would make your question easier to answer. – BigBen Jan 31 '19 at 22:55
  • I wish I could just copy and paste but I can't. Basically we have the time period for 24 hrs broken down into 3 hour blocks. If conditions are good, we leave it blank and color that time period green. If conditions are marginal, we put the weather via letters in the box and color it yellow. If conditions are bad, we put weather via a single letter in the box and color it red. Unfortunately, the same letters are used for marginal as they are for bad, so I can't color code the data that way. – Nebu10us Jan 31 '19 at 23:29
  • In the end, we take the worst case for all the blocks over 24 hours into a separate sheet. For example, if it's green in the beginning and red later on, we'd color code the entire first 24 hours as red. – Nebu10us Jan 31 '19 at 23:32
  • I have a screenshot, give me a second to figure out how to upload it. – Nebu10us Jan 31 '19 at 23:37
  • See above, thanks for any help you can give! – Nebu10us Jan 31 '19 at 23:41
  • If the letters overlap between red and yellow, I don't think you can get a full solution without VBA. You could get partially there with conditional formatting (e.g. a rule for green is easy), but not entirely. If there were letters that were red-specific (i.e. if TS can only be used for red), then you could add an additional rule for red. But again, you won't be able to cover all possibilities without VBA. – BigBen Feb 01 '19 at 00:03
  • Found this answer that may be usedul to see what the limitations of getting the color of a cell by just formulas are: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color – Ricardo Diaz Feb 01 '19 at 02:47
  • Thank you! That's what I suspected. Appreciate your insight and assistance. – Nebu10us Feb 03 '19 at 03:28

0 Answers0