Solved Excel - Conditional Formatting

September 22, 2016 at 19:09:05
Specs: Windows 7
I have got a set of data that needs to be highlighted(colour) if below the Pass Mark from each student row ( eg John- reading and Writing will turn Red because below te pass mark). I can only do one row by using the Rule but i have thousands of students, do i need to setup one rule per student? is there an easy way to do this. Many thanks !!

Name Pass Mark Reading Maths Writing etc
John 120 110 130 110
Mary 100 90 120 130
Steve 90 80 80 80

See More: Excel - Conditional Formatting

Reply ↓  Report •

September 23, 2016 at 06:18:42
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. The instructions will explain the use of the pre tags to get the columns to line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Based on the layout below, follow these steps:

1 - Select C2:E4
2 - Open the CF dialog box and choose New Rule
3 - Choose the "Use a formula..." option
4 - Enter =C2<$B2 in the "Format values where..." field
5 - Click the Format button
6 - Click the Fiil tab
7 - Click Red
8 - Click OK then OK

The CF rule will be applied to all selected cells and will compare each cell to Column B of the same Row. The $ locks the reference to Column B but all other references will "float" based on the Column and Row of each cell.

      A        B           C          D          E   
1   Name   Pass Mark    Reading     Maths     Writing     etc 
2   John      120         110        130        110
3   Mary      100          90        120        130
4   Steve      90          80         80         80 

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

September 23, 2016 at 06:34:41
Thanks very're a star!

Reply ↓  Report •

Related Solutions

Ask Question