Conditional Formatting and Vailidation Challenge

**haleakala17** · 07-31-2012, 08:40 PM

On my Main Excel workbook I have 2 columns on my Main worksheet (Name and Quantity). I am requesting if anyone knows a formula in Conditional Formatting which will turn the font color red for the sum of numbers listed in the Quantity column for each person, if it is less (<) or greater (>) than 100. Example, the total sum of numbers for McCARTHY, JENNIFER is 101 so the font colors for the numbers (95 in cell B4 and 6 in B5) should turn red, view yellow highlighted rows in the Main spreadsheet. If there is a way to create a formula in the Data Validation to prevent a user from entering numbers for a person that does not equal to 100, this will be awesome!

FYI - I have created a pivotable in the SUM worksheet to track the totals in the Quantity column for each person. The person's name is selected from a drop down list which is placed in the List worksheet.

I appreciate the time and assistance in this matter.

**Ace_XL** · 08-01-2012, 02:16 AM

You dont need the pivot table

In Conditional formatting use
=SUMIF($A$2:$A$15,A2,$B$2:$B$15)<>100 ---> format Red

**haleakala17** · 08-01-2012, 09:32 PM

Thank you very much ACE_XL, this formula worked perfect! I really appreciate your time and assistance on this matter. You got me hooked on Excel and I am trying to create some basic formulas. With more practice, I am sure to get better of it. I am even thinking about learning some VB stuff. If you got any links for advice to send me in regards to building my skills in Excel and VB, I would appreciate it if you could send it my way.

**Ace_XL** · 08-02-2012, 02:56 AM

Welomce to the forum haleakala! Glad to hear that you are hooked onto Excel.

For starts, kindly mark the thread solved
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Below are links in the forum to some really useful Excel tips and learning...Enjoy!!
http://www.excelforum.com/excel-gene...additions.html
http://www.excelforum.com/excel-gene...additions.html

Edit: Thread is already marked as 'solved'