# Conditional Formatting Using Percentages

Highlighted
Occasional Contributor

# Conditional Formatting Using Percentages

I need to highlight a cell that is 10% over another cell.

example

B1 is \$100

B2 is \$110

i need B2 to be highlighted. since its 10% higher.

I know how to do this if put in with exact numbers but i want it done in percentage for

8 Replies
Highlighted

# Re: Conditional Formatting Using Percentages

You may try the following formula for making a new rule for conditional formatting...

=B2>=B1*1.1

Highlighted

# Re: Conditional Formatting Using Percentages

WORKED GREAT!!!!

Next Question:

How can I get it to work for the rest of my cells so i don't have to input it for every single one.

example:

B1 \$100

B2 \$110      =B2>=B1*1.1

B3 \$200

B4 \$220      =B4>=B3*1.1

B5 \$300

B6 \$330      =B6>=B5*1.1

I need B2 to change, B4 to change, & B6 to change. I can't copy the formatting because it only copies the original formatting and doesn't continue it down the line with new cells. Any suggestions?

I have 10 workbooks to do this to with about 200+ cells and I dont want to spend weeks on this.

Highlighted

# Re: Conditional Formatting Using Percentages

Hello @MiMiMarie,

With the conditional format applied to only B2, select B1 and B2. Then select the format painter (looks like a paint brush in the home tab) and select your entire list. This will apply your format to every other cell starting at B2.

P.S. You may double click the format painter to use it multiple times

Highlighted

# Re: Conditional Formatting Using Percentages

That didn't work. It only copied the original format with the original cells in the format. @PReagan

Highlighted

# Re: Conditional Formatting Using Percentages

Make sure that the conditional format in B2 does not use absolute references. The conditional format in B2 should be =B2>=B1*1.1, not =\$B\$2>=\$B\$1*1.1

Highlighted

# Re: Conditional Formatting Using Percentages

In that case you may select the range B2:B6 and make a new rule for conditional formatting using the formula given below...

=AND(MOD(ROW(),2)=0,B2>=B1*1.1)

Highlighted

# Re: Conditional Formatting Using Percentages

As a comment - Format Painter will create a bunch of new rules, better to apply formatting to entire range.

Highlighted

# Re: Conditional Formatting Using Percentages

Thank you for your comment. You are correct.

With that in mind, @Subodh_Tiwari_sktneer offers a much more elegant solution.