Excel provides a very useful feature for set row’s color according condition based. For example user maintains excel sheet of student list based on pass and fail. User wants to set passed student’s row color become green when he entered status as pass and on fail set row color as red. In another example, change row color when date will be expired, etc. Steps are given below.

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.

Note:

      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

Number Based

Greater Than:

=$B1>75

Less Than:

=$B1<56

Date Based

To check if a date is in the last 7 days:

=TODAY()-A1<7

To check if a date is in the current week:

=AND(WEEKNUM(A1)=WEEKNUM(TODAY()), YEAR(A1)=YEAR(TODAY()))

To check if a date is in the current month:

=AND(MONTH(A1)=MONTH(TODAY()), YEAR(A1)=YEAR(TODAY()))

To check if a date is in the last 30 days:

=TODAY()-A1<30

You can easily search formula and apply here.