Change cell colours in a worksheet if values exist in another worksheet

[DigiBlurk]

Hi,

I am working on a project where the first worksheet contains details of employees, column B has the office number they work in, i.e. 7132.

The second worksheet is a basic floor plan of an office where different cells have the office numbers in them. The floor plan cover cells from A1 to CP150.

I am trying to highlight (red) in the second worksheet all the cells where the value appears in column B of the first worksheet. I can do it with Conditional Formatting but it only works if all the data is in one worksheet.

There's other criteria such as Column C containing when the office was unoccupied if it's within 30 days of the today then I am trying to highlight that in yellow, because of this I am figuring VBA is the way to go, but I am really struggling to think of some script for it.

Any help would be greatly appreciated.

Si.

[millz]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[etaf]

a sample sheet would help
CF can work across sheets

I have a sample here using countif()

BUT your spreadsheet would be useful - with examples of results expected

[DigiBlurk]

Hi and Thank you,

I hope this demonstrates what I am trying to explain. I had to create a simplified version as there is sensitive info in the original.

Sheet one has the employee info with column B holding the Office number.

Sheet two has a floor plan with the office numbers in it.

If the office number exists in both then it is highlighted in Sheet 2, if it is unoccupied but the date is with 30 days it is highlighted yellow, if it is unoccupied it remains unfilled.

Many thanks,

[millz]

Try running this on Sheet1:

Please Login or Register  to view this content.