Conditional formatting based on formula

Drwats0n

New Member
Joined
Dec 10, 2015
Messages
38
Hi All,

=$R4>Q4>$R4*1.1

I need to apply the above formula in conditional formatting to highlight the field if the above formula is met.
However, i need to apply the formula from row 4 to row 13 for both R column and Q column.

My question is, instead of going into each cell and update the formula in conditional formatting based on the row number that i need, is there a quicker way to let conditional formatting update the formula to match the row number that i need?

In non conditional formatting scenario, i simply just drag the formulas down to update the row number. But for conditional formatting what is the best way to do it?

Hope my explanation is clear, thanks all in advance! :)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,313
Office Version
  1. 365
Platform
  1. Windows
Simply select Q4:R13 & then apply the format rule
Although you will need to lock the Q col
=$R4>$Q4>$R4*1.1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,514
Office Version
  1. 365
Platform
  1. Windows
If you select the entire range you want to apply it to first, then enter the formula as it applies to the very first cell in your range, Excel will automatically adjust it for all other cells, provided that you use the absolute/relative range referencing correctly.

So highlight all the rows you want to apply it to and enter this CF formula:
Excel Formula:
=AND($R4>Q4,$Q4>$R4*1.1)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top