How to show the cell in different color due to other cell's result?

[akinkaraman]

Row 5 shows the results due to Index Unit table below.

I want to change the colors in Index Table's cells due to the Total Zone Effects table above which are shown due to Index Unit table.

Such as If Zone E shows 4500 kgs it sees between 4001 - 4500 and finds ''-2'' as a result from H18 and shows it at H5 than I want to show H18 as a same color of H5.

Can you help me to do that?

 

[Hui]

Select C9:N27
Conditional formatting
New Rule
use a Formula
=$B9=VLOOKUP(C$4,$B$9:$B$27,1)
Select a Format
Apply

See attached file:

 

[akinkaraman]

I don't want to change B rows between B9 to B27.

I just want to do this without changing it.

 

[Swapnil]

Select C9:N27

use following formula while applying conditional formatting

=((C$4>(--LEFT($B9,FIND(" ",$B9,1)-1)-1))*(C$4<(--RIGHT($B9,LEN($B9)-FIND("-",$B9,1)-1)+1)))=1

 

[Hui]

Akinkaraman

I understand what you want.

In my experience, it is poor layout of spreadsheets that is the main cause of poor performance, lack of flexibility, complex formulas etc

In your case, why not add an adjacent column with some text concatenation


ie: C9: =" - "&B9
copy down

 

[akinkaraman]

Select C9:N27

use following formula while applying conditional formatting

=((C$4>(--LEFT($B9,FIND(" ",$B9,1)-1)-1))*(C$4<(--RIGHT($B9,LEN($B9)-FIND("-",$B9,1)-1)+1)))=1

It is working good. Thank you..