Forum Discussion

Shilpa Dave's avatar
Shilpa Dave
Copper Contributor
Jun 03, 2017

Spreadsheet help with formulas to change background color

I am struggling to add a background color to my sheet. I have below requirement.

Sheet1 has the following data:

Date and TimeData1Data2Data3Data4
6/1/2017 7:00010.523
6/1/2017 7:440123
6/1/2017 7:45020.523
6/1/2017 7:460123
6/1/2017 7:4933123
6/1/2017 7:44012233
6/1/2017 7:45030.523

 

Sheet2 has the following data:

CycleDate and Time
Cycle16/1/2017 7:44
Cycle26/1/2017 7:49
Cycle3

6/1/2017 7:50

 

A requirement is first I need to match Date and Time from sheet1 to sheet2 then add the background color "RED" to sheet1.

example,

Match cycle1 date and time from sheet2 to sheet1.

Match cycle2 date and time from sheet2 to sheet1.

Between Cycle1 and Cycle2 data, rows need to change the background color. (means all data row 44,45,46,49 has to be in RED color).

 

I tried different equations but do not able to change it.

 

 

 

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 03, 2017

    Hi Shilpa,

     

    You may use Conditional formatting. In simplest variant, assuming date and time is in column A of Sheet1 and column B of Sheet2 add the rule to Conditional formatting

    =($A2>=Sheet2!$B$2)*($A2<=Sheet2!$B$3)

    and apply it to all cells with data in Sheet1

    =$A$2:$E$9

    if i calculated correctly.

     

    Instead of hardcoding Sheet2!$B$2 (b3) you may find their values based on cycles names, perhaps other improvements - depends on how flexible all above shall be.

     

     

     

     

    • Shilpa Dave's avatar
      Shilpa Dave
      Copper Contributor
      Jun 04, 2017

      Thank you, Sergei.

       

      I tried the same function before but did not work and did not get and highlight between two range of date.  I tried also LOOKUP and MATCH but it gives me single matching column but not full block.

      Also, I tried using Conditional Formating with different options such as Between, creating new rule but none of the function works for a particular condition.

       

       

      Example,

      1) Take value from Sheet2 (Cell B2)

      2) Check on Sheet1 (Cell A2:A12)

      3) Select all Rows in Sheet1 if Sheet2!B2 is less than value in Sheet1!A2:A12

      4) Highlight selected ROWS with RED Color.

       

      Thank You!

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 05, 2017

        Hi Shilpa,

         

        On the picture and in the attached file step by step formula modification for your first example. Latest formulas assume you define between which cycles to color in column G of the Sheet1, by INDEX/MATCH you find time for defined cycles in Sheet2 and compare them in conditional formatting formula with times in column A of Sheet1. If the time is within the range

        =($Ai >= cycle1 time)*($Ai >= cycle2 time)

        formatting is applied.

         

         

         

         

Resources