Step 1: Entered two data into excel sheet as below image. Here I have changed row color based on pass and fail value.
Step 2: Select area where you want to see effect of row color. Below I have selected A1xB2 area.
Step 3: Now select “Conditional Formatting” tag from “Home” menu as following image and select “New Rule…” option.
Step 4: When you selected “New Rule…” option, one popup window will be appears, where you apply various formula as below.
1. Select “Use a formula to determine which cells to format” option for apply custom formula.
2. =$B1=”pass” is a formula that set the color of row. Here “B” indicate column index, which used for apply formula and “1” is show active cell index. Active cell index you can get when you select area.
3. Set row color and styles using “Format” button.
Step 5: When you press “OK” button in step 4, you see effect in your selected area as below image.
Step 6: Follow step 4 for “fail” condition as below image.
Step 7: Insert some new record and check your applied formula and see effect as below image.
Note: Below I have given some important formula
To check if a date is in the last 7 days:
To check if a date is in the current week:
To check if a date is in the current month:
To check if a date is in the last 30 days:
You can easily search formula and apply here.