Conditional Formatting works only after highlighting the table

[sabrez]

Hi,

I am working on a Price Calculator. I need to highlight rows in the below table based on the value of a particular cell $D$6

Pricing Tiers	Multiplier	Low unit price	Mid unit price	High unit price	Mid price sale Total
Level 1 Pricing	AMF Cost x 2.5 to 3 times	$22.85	$25.13	$27.42	$2,741.69
Level 2 Pricing	AMF Cost x 3.5 to 4 times	$31.99	$34.27	$36.56	$3,655.58
Level 3 Pricing	AMF Cost x 4.5 to 5 times	$41.13	$43.41	$45.69	$4,569.48
Level 4 Pricing	AMF Cost x 5.5 to 6 times	$50.26	$52.55	$54.83	$5,483.38
Level 5 Pricing	AMF Cost x 6.5 to 7 times	$59.40	$61.69	$63.97	$6,397.27
Level 6 Pricing	AMF Cost x 7.5 to 8 times	$68.54	$70.83	$73.11	$7,311.17
Level 7 Pricing	AMF Cost x 8.5 to 9 times	$77.68	$79.97	$82.25	$8,225.06

<tbody>
</tbody>

If $D$6=1, row 1 should be highlighted, if $D$6=2, row 2 to be highlighted and so on.

Any help would be highly appreciated.

 

[Chrisdontm]

Try this;

Highlight the whole area you want included in the conditional formatting,
Click: " Conditional Formatting "
Click: " New Rule "
Click: " Use a formula to determine which cells to format "

In the rule description bar enter this formula;
=IF(ROW(A9)=$D$6,TRUE,FALSE)

In the formula, replace " A9 " with the cell reference to the top left corner cell reference that you highlighted.

Click: " Format "

Then choose your color and or pattern, border, font, or numbering format that you would like.

Click " Ok ", and " Ok "

That should get you there...

 

[sabrez]

Hi Chris

Thanks for your reply.

Well, the formula works only AFTER I select the table in question.

To make myself clear, I input a value, say 2, in cell $D$6 and press enter- nothing happens. And then, when I select the table A1:F8, the second row (A3:F3) gets highlighted.

Any idea how to get around this?

Regards
Sabrez

 

[Norie]

Sabrez

Why would you put a value in D6? Isn't that in the middle of the table in A1:F8?

 

[sabrez]

Sorry, my bad.

I would put a value in a cell NOT in table A1:F8, say D12, and based on the value of D12, respective row in table should be highlighted. Row 1 if D12=1, row 2 if D12=2, and so on

 

[Norie]

This might be a stupid question but do you have Automatic Calculation turned off?

PS Is the header row to be counted as a row or is row 1 actually the first row of data?

 

[sabrez]

Hi Norie

Calculation Options is set to Automatic- all other formulas in the worksheet working fine.

Please discard header row, row 1 is the actual first row of data (A1= Level 1 Pricing)

 

[Norie]

It's working for me, see this workbook.

 

[sabrez]

It's working for me, see this workbook.

Hi Norie

Thanks heaps. Works perfectly now.

Used your formula =ROW(A2)=$D$6+1

 

[sabrez]

Hi Norie

Can you please check this https://app.box.com/file/267377438734? You can ignore the warning about Updating external links, if you get any.

The first sheet, Current Client Quote, has the table (A12:G19) where rows are to be highlighted, depending on the value of cell D6.

For me, this is only working after selecting the entire table. However, the formula works perfectly fine on another worksheet (Sheet2- last tab)

Any idea why is this happening?