Background changes conditionally

[PeterG]

Hi all,

I have a spreadsheet that shows the floor plan layout of my call centre.
On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet.

I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address.

How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167

any ideas?

[keithl816]

Hi peterg,

If the cell vaule is subnet 162 or subnet 167 you could try this

Place this in the cell subnet 162 etc.. would appear
Try Format/Conditional Formatting/Cell Value is/Equal to/Subnet 162/Click on Format button/Click on Patterns Tab/Pick your color

Click the add button and repeat Cell Value is/Equal to/Subnet 167/click on format Button/Click on Patterns tab/Pick your color
You can do this up to three different conditions.

If the cell value is in another cell besides your target cell then you could try this

Place this in the cell you want highlighted
Format/Conditional Formatting/Formula Is/=b1="subnet 162"/Click on Formatt Button/Click on Patterns Tab/Pick your color and so on.
Replace b1 with the cell you are referencing.

Hope this helps

[PeterG]

Thanks for that.

Unfortunately the subnet 162 & subnet167 details are contained on another worksheet within the spreadsheet.

[greg7468]

Peter,
could you not put the subnet number in a cell adjacent to the desk, maybe keep the text colour the same as the background so it is not visible.
You could then format using this cells value as above.

HTH.