?
Solved

Use a formula to apply conditional formatting in Excel

Posted on 2014-09-13
5
Medium Priority
?
223 Views
Last Modified: 2014-09-23
Hi everyone,

I would like to use conditional formatting in Excel to Bold certain cells based on their value.

I have tried to use excels built in conditional formatting function but it seems awkward to have to format every single cell since I am using different formatting rules for each cell. Then I would have to manually do this for the entire table, thus it becomes very cumbersome to do manually.

For instance, the attached file cells are to be formatted as follows:

Cell #                               Format Bold if ...

C6     ......................         greater than 0
D6    ......................          negative number
E6    ......................          negative number
F6    ......................          negative number
G6    ......................          negative number
H6    ......................         greater than 0
I6      ......................         greater than 0

Is there a formula for this formatting?
EE-CellFormatBasedonValue.xlsx
0
Comment
Question by:Pedrov664
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 1000 total points
ID: 40321324
In C6 select Conditional Formatting and go New Rule - Use formula to determine which cells to format. In the formula bar enter =C6>0. Select Format - Font Style-Bold. Select OK. Copy C6, highlight H6 & I6, right-click H6 and select Paste Special. Select Formats then OK. Do the same for D6, just use the formula =D6<0.

Flyster
0
 

Author Comment

by:Pedrov664
ID: 40321330
Yes, but there is a formula there already. Which is how I get the result you see. I would like to know how to use a formula without erasing the formula already there. It also must be able to copied to other cells so I can fill the table with same format.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40321332
A formula in a cell is different from a formula in conditional formatting. You can also use multiple formulae in conditional formatting.
0
 

Author Comment

by:Pedrov664
ID: 40323254
Flyster,

As I stated above I used "excels built in conditional formatting function but it seems awkward to have to format every single cell since I am using different formatting rules for each cell".

I tried highlighting a column and applying conditional formatting that way but selecting an entire column does not seem to work the way I want it to.

It is a bit cumbersome but it seems to work. I was hoping for an easier way to apply the format since I am doing an only several cells in an entire table.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1000 total points
ID: 40338134
Don't know if you still needed this solution, but instead of entering a formula in the conditional formatting rule you could specify to format cells whose value are greater/less than zero.

1) Select all the cells you'd like to apply the conditional format (use [Ctrl]+click to select multiple cells).
For example, select cells C6, H6, I6 (and maybe also K6, P6, and Q6)
2) Select from the menu Home - Conditional Formatting, New Rule
3) Select "Format only cells that contain" from the top
4) change "between" to "greater than"
5) enter "0" in the box to the right
6) Click the "Format" button and select Bold for the font. Click OK.  The dialog box should look like this:
conditional formatting - greater than zero7) Click OK.

That's it; one rule for all the cells greater than zero.  You can repeat this process for cells to format (bold) with values less than zero also.  Lastly, if you call up the "Manage Rules" option under conditional formatting, you can edit the formula criteria and the ranges affected.
conditional formatting - all criteria
Regards,
-Glenn
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question