Search code examples
excelexcel-formulaexcel-2013forex

Using conditional formatting with multiple conditions to color format cells

I am trying to find the most efficient way to give my times a color code (4 different colors) based on what trading session the alert was triggered (London, London & USA, USA, and After Hours). I have tried a number of ways but none have provided a proper result.

For example, if an alert was triggered at 07:05:33, then that triggered occurred in the LONDON & USA session and should be colored green. If an alert was at 02:22:33, then that was in the London session and that field should be colored blue. These are just a few examples. If no time value is provided, then the appropriate field needs to remain unfilled. I wanted to include a photo but haven't built up enough reputation points as of yet. So... I am going to try and give a verbal snapshot of what I have and describe it to the best of my ability.

My alert signals are located e11:e61... the E column is the one that I would like to color.

Sessions are as follows...

London Only    2:00:00 - 6:59:59  (Dark Blue)
London & USA   7:00:00 - 9:59:59  (Green)
USA Only       10:00:00 - 4:00:00 (Dark Orange)
After Hours    4:00:01 - 23:59:59 (Light Blue)
After Hours    0:00:01 - 01:59:59

Not sure how I can program this entire column in Excel to do this. I have seen multiple colors on spreadsheets, but do not know if it is manual or automatic. I would assume automatic due to the amount of data and time that it would take to color each of the cells.

Didn't know if I could program this directly by the input cells. If not, I also set up a small session reference table. 

J5:K5 represent 2:00:00 - 06:59:59
J6:K6 represent 07:00:00 - 09:59:59
J7:K7 represent 10:00:00 - 4:00:00
J8:K8 represent 04:00:01 - 23:59:59
J9:K9 represent 00:00:01 - 01:59:59

Not sure it is needed, if not... I will remove. Whatever suggestions / recommendations would be a huge help.

Solution

  • I was able to solve this problem by following the below steps:

    1. Home>Style>Conditional Formatting
    2. Create New Rule - "Use a formula to determine which cells to format"
    3. I had to create 5 different rules due to my session time setup.
    4. Each rule followed the basic setup: =AND($e11>=TIME(7,0,0),$e11<=TIME(9,59,59))
    5. I only had to change the time data for each of the five.

    It is working perfectly now! Hope that this is hellpful to others.