# Conditional Formating for Charity Hostel (really need help please)

#### Harters

##### New Member
Hi

We are a charity running emergency shelter hostels.

We have an issue I am trying to solve where a cell in sheet2 will carry a value which exists in a list in sheet1.

I would like to colour this cell in sheet2 based on a value in sheet1.

So if a cell on sheet 2 has the word Apple Street, the formating for that cell needs to look at sheet1, do a kind of vlookup to find the relevant word (Water in 1st example) and then format the color based on that. If the Apple street hostel then later needs more soup to be delivered, we will change Water for Soup on sheet1 and this should change the colour of the cell on sheet 2 with the word Apple Street in it to a different colour.

There are only 5 items that could be listed in the "Need" column

is this possible ???

Sheet1

 Hostel Need Column Apple Street Water Orange Street Clothes High Street Bread

<tbody>
</tbody>

Sheet2

Apple street is coloured BLUE
 Apple Street Orange Street High Street

<tbody>
</tbody>

Sheet1 for Apple street is then changed from Water to Soup
 ObHostel Need Column Apple Street Soup Orange Street Clothes main Street Bread

<tbody>
</tbody>

Sheet2 should now change - now Apple Street is now shown in RED
 Apple Street Orange Street Main Street

<tbody>
</tbody>

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
This should work (untested)
Assuming top left corner of all tables is A1...

Select Sheet2!B2 (Apple Street)

Conditional Formatting
New Rule
Use a formula to determine...

=(VLOOKUP(Sheet2!B2,Sheet1!A\$2:B\$8,2,0)="Water")
format as Blue

=(VLOOKUP(Sheet2!B2,Sheet1!A\$2:B\$8,2,0)="Soup")
format as Red

Add other conditions (Water, Soup with appropriate colours as required

Thats brilliant my friend. I can't thank you enough... many people will get help they need more efficiently because of you helping us...God bless you

Replies
4
Views
777
Replies
1
Views
419
Replies
1
Views
786
Replies
6
Views
530
Replies
1
Views
450

Threads
1,203,455
Messages
6,055,540
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back