Use a formula to apply conditional formatting in Excel

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
Pedrov664Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
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
Pedrov664Author Commented:
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
FlysterCommented:
A formula in a cell is different from a formula in conditional formatting. You can also use multiple formulae in conditional formatting.
0
Pedrov664Author Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.