Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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
0
Pedrov664
Asked:
Pedrov664
  • 2
  • 2
2 Solutions
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now