# Conditional formatting Vloolup/Match inside to change Colour

1. ## Conditional formatting Vloolup/Match inside to change Colour

Good afternoon

I have been looking on the net to use conditional formatting Cant seem to find the right guide

I am trying to get Column A to look up its self the truck number and match it to Column j and M and going on the cell next to it the colour would change
Green for PIK Red for REL

I have tried to use =\$N3="PIK" which was working Then I remember some times we have a truck that is not on the list so we add it in e.g. Truck 14A Truck3A in Column A

which then bugs out =\$N3="PIK" and it changes some of the colours

I think I need to use a vlookup or a Match

Any help would be great

2. ## Re: Conditional formatting Vloolup/Match inside to change Colour

1. Select cell A1.
2. Create the first formatting rule (green) using this formula: =IFERROR(VLOOKUP(A1,\$J\$3:\$K\$27,2,0)="PIK",VLOOKUP(A1,\$M\$3:\$N\$27,2,0)="PIK")
3. Set the Applies To field to =\$A:\$A.
4. Create the second formatting rule (red) using this formula: =IFERROR(VLOOKUP(A1,\$J\$3:\$K\$27,2,0)="REL",VLOOKUP(A1,\$M\$3:\$N\$27,2,0)="REL")
5. Set the Applies To field to =\$A:\$A.

All done. You will have to delete all your attempted rules first, or they will interfere with the results.

3. ## Re: Conditional formatting Vloolup/Match inside to change Colour

Sample of the above now attached.

4. ## Re: Conditional formatting Vloolup/Match inside to change Colour

Good afternoon AliGW

You are the best did not know you could do 2 vlookups with inside the 1 formula
After getting this to work on my sample book I have changed all the cells to match my main sheet
=IFERROR(VLOOKUP(B1,'Pick order'!\$L\$4:\$M\$28,2,0)="REL",VLOOKUP(B1,'Pick order'!\$O\$4:\$P\$28,2,0)="REL")

Thanks again for all your help
JustinwB

5. ## Re: Conditional formatting Vloolup/Match inside to change Colour

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1