Forum Discussion
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 Time Data1 Data2 Data3 Data4 6/1/2017 7:00 0 10.5 2 3 6/1/2...
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.
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!
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.
Sergei,
Thank you so much of detail steps. I tried and it is working fine.
Thank you!
Shilpa
