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?


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=381545

Hi,
assuming that your data sheet has the desk number in B1 and the subnet
number is in E1

conditional format your desk colour =E1=162 (format 1 colour) =E1=167
(format second colour)

HTH.


--
greg7468


------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=381545

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


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=381545

Thanks for that.

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


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=381545

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.


--
greg7468


------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=381545