Conditional formatting using vlookup

[Luke123]

I have a worksheet that has 3 tables, each table is auto populated from a raw data import. I have figured out a way to do conditional formatting on one cell now I need a way to copy that formatting to 108 other cells with it updating the cells without creating 324 more rules.


The formatting is as follows
Green Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)>10
Yellow Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)>2.5
Red Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)<2.5
With each set to stop if true.


Is there a better way to do this formatting without vlookup or is there a way to copy this formatting without having to create 324 more rules. My next cell that needs this would be D5 with D6 being the lookup. Most of the layout are across with the lookup below the cell needing formatting

 

[FDibbins]

Hi, welcome to the board

use the "applies to" box to indicate the entire range you need

 

[Luke123]

Yeah did that but doesn't update the formula of the conditional formatting

 

[FDibbins]

What does your rule look like with that suggested change?

 

[Guru008]

Hi,

I guess you want to format cells values greater than 10, between 2.5 and 10 and less than 2.5.

To do that select all the rows you want to format, go to conditional formatting choose cell greater than option and choose format and press add, now repeat the step with between and less than option and add these formats. Its done

 

[FDibbins]

Hi,

I guess you want to format cells values greater than 10, between 2.5 and 10 and less than 2.5.

To do that select all the rows you want to format, go to conditional formatting choose cell greater than option and choose format and press add, now repeat the step with between and less than option and add these formats. Its done

OP already has a set of (working?) rules in place...

The formatting is as follows
Green Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)>10
Yellow Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)>2.5
Red Apply to. =$C$5
=vlookup(C6,table!A:G,6,False)<2.5
With each set to stop if true.

They are trying to have this adjust as it moves across (I think)

 

[Luke123]

Yes I need the formula for the formatting to change from C6 to D6 then E6.... so on for 108 cells total writing all the formulas by hand would be a pain and if something was to change the list of formatting conditions would be 324 entries to look through. Not sure if there is another way, the cells that are being conditional formatted is a vlookup from raw data which looks at the cell below as it doesn't change. The information that is displayed is a 7 digit number. I need to look back at the raw data at the 7 digit number which in the raw data has usage I want to show if my usage it's greater than 10 it's "green" between 10 and 2.5 it's "yellow" and below 2.5 its "red"

 

[FDibbins]

Would the vlookup change?
(trying to understand what you want lol)

 

[Luke123]

So currently the conditional formatting formulas are in cell C5 here is one of the three conditions =vlookup(C6,table!A:G,6,False)>10 and it turns the cell green. Then I have a second formula for yellow then one for red. I need the formula to change to =vlookup(___,table!A:G,6,False)>10 I need the blank to be D6,E6,F6.... Is there a way to write in conditional formatting =vlookup(cell below,table!A:G,6,False)>10