# Conditional format on basis of other cells color

#### excel_1317

##### Board Regular
I have students data with their %marks in each Subject. I have applied conditional formatting on the basis os marks percentage. eg: Green if Marks is above 80 %, Yellow is marks in between 40% to 60%, Red if marks ia below 40%.

Now in first column, there is students names. I want if i have total of 6 subjects, student having 5 or 6 green subjects should turn the name in Green, Student having 3 or 4 Green subjects, should turn his name Yellow

and lastly, 1 or 2 subjects Green, should turn student name into RED...

Is this something achievable?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### DRSteele

##### Well-known Member
I think you meant >80 is green, 40-80 is yellow and <40 is red. So this might do it. Select A2 through A7|Conditional Formatting|New Rule|Use Formula|Format Values...|paste this in =COUNTIFS(\$B2:\$G2,">=80")>4 and the desired format to green|selct OK. Then do new rule just like that one but paste this in =OR(COUNTIFS(\$B2:\$G2,">=80")=3,COUNTIFS(\$B2:\$G2,">=80")=4) and the desired format to yellow. Repeat creating new rule =OR(COUNTIFS(\$B2:\$G2,">=80")=1,COUNTIFS(\$B2:\$G2,">=80")=2) and the desired format to red.

Excel 2012
ABCDEFG
1StudentECON101MATH101ENGL101STAT101WOMN101PSYC101
2Abe8210064908590
3Bill386590515173
4Carl819438904070
5Dave457547784591
6Elon663852917551
7Fred934156723246
Sheet19

Replies
1
Views
493
Replies
1
Views
167
Replies
15
Views
1K
Replies
2
Views
298
Replies
1
Views
871

1,196,010
Messages
6,012,843
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back