You are here:

Excel/Conditional Formatting

Advertisement


Question
Hi Andrea,

Thanks a lot for previous help.

An extension to help on previous Question; this time i want to use conditional formatting to color the cells between Jan-Dec as below which fall below the criteria of less than Ave 2012 divide 2 formula. This means that the months which have less than 135/2 ie 65
should be colored red.

J  F  M  A  M   J   J   A   S  O  N   D    Ave 2012
80 60 35 46 79 120 180 65  103 37 66 187      130

i manage to do for row by row. Is it possible to do for all the rows at one go? i have about 1000 rows of this database!!

Thanks in advance!!

Answer
Yana -

Hello again, welcome back! You may already have done most of what is needed for this.

Apply Conditional Formatting to the first row:
1. select the cells (C1:M1) but not the cell that has the 2012 number.
2. Home tab > Conditional Formatting > Highlight Cells Rules > Less Than...
3. Format Cells That Are Less Than - F1/2
4. Pick the color you want the affected cells to become
That's the first part.

Second part:
1. Go into Conditional Formatting > Manage Rules > Show formatting rules for - This Worksheet. Change Applies to for the entire table - say, $C$1:$M$1000. Don't worry about changing the rule, it will adjust for that.
2. Poof. Your Conditional Formatting has been painted onto your entire table.

If things don't seem to be working right, go into Conditional Formatting > Manage Rules > Show formatting rules for - This Worksheet. If the Rules box has quotation marks in it, Edit and remove them. That should clear up the problem.

Let me know how it goes - Andrea
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.