Conditional Format: Want Respective suppliers in list turn into red

Hi AliGW,

Thanks I have just amend it with more detail. Hope now someone will help me out. I have really get my head around it but couldn't get the solutions

Thanks Again

Hi protonLeah,

Thanks, but what I am after is actually very simple. I want when Limit remaining column in (Mechanical limit sheet) reduced to zero or below in negative the respective suppliers in (Mechanical sheet) turn into red. I do not need to calculate the sum of these suppliers as it is being already calculated into Mechanical limit sheet.

Thanks again

Hi

Can someone please answer.

Thanks

Hi AliGW,

Thanks, I have done and also write little more description for clarity. Hope this should be fine now.

Thanks again

Select all the cells from $D$6:$D$454 then click on Conditional Formatting | New Rule | Use a formula... , then enter this formula in the dialogue box which pops up:

=AND(C6<>"",VLOOKUP(C6,'Mechanical Limit'!$B$3:$F$21,5,0)<=0)

Click on the Format button | Fill tab and choose red. You might also want to change the foreground colour to white and bold, by clicking on the font tab. Then click on OK twice to exit the dialogue box, and Excel will automatically adjust the cell references to suit the cells you had selected initially, so you only need to do this once.

Then to test it out you could manually adjust the credit remaining for City Plumbing, say, on the other sheet to zero or negative, and then see the results on the Mechanical sheet.

Hope this helps.

Pete

Hi Pete,

Fantastic ! and absolutely stunning. Much appreciated.

Thanks to all.

You're welcome, and thanks for marking the thread as Solved.

As you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete