# Conditional formating with a formula

#### drangle

##### New Member
I have a spreadsheet of 1400 students and I'm trying to find which ones are eligible for our program. I have adjusted gross income in one column and dependents in another column. I have a countifs that accurately tells me how how many are eligible. But I can't figure out how to highlight the eligible ones. Help, please:

<tbody>
</tbody>

#### drangle

##### New Member
Here is part of the data showing the two columns in question. I am trying to highlight the students whose parents income meet our income/dependents critera.

 PARENT AGI DEPENDENCY STU EXEMPT PAR EXEMPT I 4 9984 D 2 65448 X 1 6 I 4 41841 D 2 X I 4 I 4 23710 D 2 42858 X 0 5 I I 2 I 1 I 5 I 34817 D 0 4 I 34450 D 4 D 68128 D 0 0 28734 D 4 18755 D 1 4 25000 D 0 2

<tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### ClaireS

##### Board Regular
So Columns F and I are "Parent Agi" and "Par Exempt"? Again, there is no entry where the "Par Exempt" = 3, so no rows will be formatted. If the "Par Exempt" should be LESS THAN OR EQUAL TO 3, then your formula is:
Code:
``[LEFT][COLOR=#222222][FONT=Verdana]=AND(\$F2<31171,\$I2<=3)[/FONT][/COLOR][/LEFT]``
Tweak the numbers and operators to suit:
= Equal to
< Less than
<= Less than or Equal to
> Greater than
>= Greater than or equal to
<> Not equal to.

Good luck!

#### drangle

##### New Member
Yes, but as in the previous post, I do have cells highlighted that were out of the range. This one, lower in the spreadsheet, was highlighted.

 91962 D 5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

#### ClaireS

##### Board Regular
I think the CF is confused by all the blank cells. Try this (adjusting for values as necessary):
Code:
``[FONT=Verdana]AND(ISNUMBER(\$F2),\$F2<=31171,ISNUMBER(\$I2),\$I2<3)[/FONT]``

If this still doesn't give the results you expect, please explain your criteria very carefully, remembering that other people on the forum don't know your processes.

Last edited:

#### drangle

##### New Member

I think the CF is confused by all the blank cells. Try this (adjusting for values as necessary):
Code:
``[FONT=Verdana]AND(ISNUMBER(\$F2),\$F2<=31171,ISNUMBER(\$I2),\$I2<3)[/FONT]``

Okay, I finally got it to work by changing the highlight color. I was using yellow and changed to a darker gray. It worked fine. It is strange to me but I presume there is an explanation somewhere.

D

#### Fluff

##### MrExcel MVP, Moderator
What range did select, before setting up the CF rule?

#### drangle

##### New Member

I think the CF is confused by all the blank cells. Try this (adjusting for values as necessary):
Code:
``[FONT=Verdana]AND(ISNUMBER(\$F2),\$F2<=31171,ISNUMBER(\$I2),\$I2<3)[/FONT]``

If this still doesn't give the results you expect, please explain your criteria very carefully, remembering that other people on the forum don't know your processes.

I got yellow to highlight with your formula. Thanks!!

F2:f1446

#### Fluff

##### MrExcel MVP, Moderator
That range is fine, not sure if you are aware but the formula needs to reference the first row in the applies to range. So if you had selected F1:F1446 then with the formulas supplied it would always highlight the cell above the correct cell.

Thanks.

Replies
1
Views
358
Replies
1
Views
425
Replies
6
Views
407
Replies
3
Views
680
Replies
9
Views
785

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### 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