Background changes conditionally

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?action=getinfo&userid=16485
View this thread: http://www.excelforum.com/showthread.php?threadid=381545

0
6/23/2005 11:50:41 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
287 Views

Similar Articles

[PageSpeed] 10

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?action=getinfo&userid=9031
View this thread: http://www.excelforum.com/showthread.php?threadid=381545

0
6/23/2005 12:51:07 PM
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?action=getinfo&userid=21287
View this thread: http://www.excelforum.com/showthread.php?threadid=381545

0
6/23/2005 3:05:49 PM
Thanks for that.   

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

--
Peter
-----------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1648
View this thread: http://www.excelforum.com/showthread.php?threadid=38154

0
6/23/2005 3:36:30 PM
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 no
visible. 
You could then format using this cells value as above.

HTH

--
greg746

-----------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=903
View this thread: http://www.excelforum.com/showthread.php?threadid=38154

0
6/24/2005 6:44:56 AM
Reply:

Similar Artilces:

Color Scheme changed
Using money 2006, Vista Home Premium. Recently had a 'not responding' shutdown. When I restarted Money 2006, the color scheme had changed. The Tool Bar (file edit etc) is white letters on white - invisible until mouse hover causes highlight. The menu bar is very pale blue on white - almost unreadable. I have wandered around in the tools area trying to find a color scheme setting but can't find one. As a further clue the corners of the Money 2006 window are square rather than rounded in other Vista windows. Not sure if that was the way it was before or not. I have changed th...

Excel 2007
Hi to all, I'm truly a beginner with pushing excel a bit, but what I'm trying to achieve seems logical and simple enough in my mind. I've got a simple daily sales sheet that is edited at each salepoint. I've got a drop-down list of vendors in Column A sorted in a Data Validation list so that much is straight forward, another few columns of details and {price totals} at the end, such as: Company X | Details | QTY | PriceEa | PriceTotal | I'm trying to automate a result that if the Company equals {Company1} then the total in that rows {pricetotal} gets added to a particul...

Reconnecting after workstation hardware change.
I work in a medical centre running a single SBS 2003 server, and 7 workstations all running Win XP. I recently updated three of the workstations - built new boxes, cloned the drives from each, ran the Win XP repair option, and installed the new drivers. All seemed to be going well, until I placed the new workstations into the network. Though the login process seems to operate as previously the server based software is not functioning, and the network printer cannot be accessed. In addition I cannot see the network via Windows Explorer, but I can access the internet. When I put the old...

Changing paper feed trays
I am publishing a 4 page news letter and printing it off on a HP laserjet IIID with mail merge. Now my question is I would like to print the first page (front and back) from one tray which has colored paper and print the second page (front and back) from the bottom tray which has white paper. I know in word I can use letter head paper in on tray and print the first sheet with letter head and the rest with plane paper. can this be done with publisher. I am useing publisher 2000 Deluxe. A small child turns to Ed, and exclaims: "Look! Look! A post from Ben Davis <bfdavis@da...

change the position of X-axis labels
Hi, is it possible to locate the X-axis labels below the graph? the problem is that I have negative and positive values in my bar-chart, and I don't want the labels to appear in my bars. So i'm looking for a way to display the labels blow the chart without having to cut&paste to e.g. Word and adding some text. Kind regards, Willem. Hi, Yes it is possible http://www.andypope.info/tips/tip002.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Willem" <Willem@discussions.microsoft.com> wrote in message news:A598EED7-F7CD-4F6E-8D6A...

Wnat to change email address of Public Folder email address with Exchange 2003! Get error
The HTTP service used by Public Folder is not avialable, possible are that the Public stores are not mounted and the Information Store service i snot running. Id no: c1030af Exchange System Manager Used as part of SBS 2K3 Publick folders and information services are started and working properly! Questions? 1) How to fix Public stores are not mounted? How? 2) Fixed with Exchange SP1 "Walt Smith" <walts@yahoo.com> wrote in message news:z0Nqe.59554$887.53617@tornado.socal.rr.com... > The HTTP service used by Public Folder is not avialable, possible ...

How do I change the default font in an EXISTING Excel workbook?
I know how to change it normally, but it seemed to switch on me from one day to the next in one particular workbook. I can't get it back. Any suggestions? Blue Format>Style>Normal>Modify. This is a workbook property so may be different from your default style in other workbooks. Change the Font to what you would like. NOTE: this also affects the Column and Row headers. Gord Dibben Excel MVP On Wed, 29 Dec 2004 13:51:06 -0800, "Blueski" <Blueski@discussions.microsoft.com> wrote: >I know how to change it normally, but it seemed to switch on me from one...

CRM not starting after Domain Controller change
Hi all, I added a new domain controller in my domain. This worked fine, no replication problem or any other problem. But since, CRM (which runs on another server) doesn't start. SQL is running on the CRM server. Each time I try to access CRM through IE, I get a generic error message and the following event is logged on CRM server (event id 2) : MSCRM Platform Error Report -------------------------------------------------------------------------------------------------------- Error: Défaillance irrémédiable Error Message: Défaillance irrémédiable Error ...

Changing Worksheets
Is anyone aware a keyboard shortcut that will allow you to 'move' from one worksheet to another within the wame workbook. I'm looking for something similar to the ALT+TAB used to to 'move' from a active window to a different window. Hi CTRL+PGUP CTRL+PGDOWN -- Regards Frank Kabel Frankfurt, Germany Adam Eichenberger wrote: > Is anyone aware a keyboard shortcut that will allow you > to 'move' from one worksheet to another within the wame > workbook. > > I'm looking for something similar to the ALT+TAB used to > to 'move' from a...

Conditional Format Tracker by Date
I'm building a tracker to make sure paperwork is done on time. Headers: A:Name B:IDNum C:Phone D:StartDate E:Plan F:Paperwork G:30DAuth H:90DAuth I'd like to use conditional formatting to compare the dates and color the cells for easier tracking. The compare TO date is column D. In column E, we have 7 days to write the plan so if there is no date in that column, turn red. When a date is entered turn green if within 7 days but yellow if it's past 7 days. In column F, same as column E but only 3 days for that one. (I can figure that out if I get a w...

Change Default View #2
Is it possible to determine a user's business unit and set the account entity default view depending on the business unit? I know it is not standard, I just am not sure if there is a way through javascript or the SDK to do it. Thanks You may be able to use a plug-in on the SavedQuery RetriveMultiple event. Here is an example to hide system views :- http://msdynamicscrm-e.blogspot.com/2008/02/hiding-view-in-crm-40-using-plug-in.html This is just a starting point to give you an example. You would have to check if the query being passed is to return the default view, and if so overr...

cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data sheet that is being refreshed. 5878 is the correct number of rows and 5824 is not. I can find and replace in my formulas to correct the problem, but it does it each time the data is refreshed. Any suggestions as to why? =(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878))) I gave you the INDIRECT option y'day, but received no feedback from you in that thread. Looks like you're more interested in knowing why? One simple hunc...

How do I change the Product Key code and the User name of Microsoft Office 2008 once installed on Mac OSX 10.5.1?
How do I change the Product Key code and the User name of Microsoft Office 2008 once installed on Mac OSX 10.5.1? On 2/26/08 5:15 PM, in article ee8ee56.-1@webcrossing.caR9absDaxw, "mollycat@officeformac.com" <mollycat@officeformac.com> wrote: > How do I change the Product Key code and the User name of Microsoft Office > 2008 once installed on Mac OSX 10.5.1? Delete these two files: ~:Library:Preferences:Microsoft:Office 2008:Microsoft Office 2008 Settings.plist where ~ is your home folder, and HD:Applications:Microsoft Office 2008:Office:OfficePID.plist -- Diane, ...

Conditional Formatting #3
Hi all I have this conditional format in cell BB35 =IF(BB35>$BB$37,TRUE) If I insert cells between BB35 & BB37, then the formula changes to =IF(BB35>$BB$38,TRUE) Is there a way of making BB37 absolute, or is there a formula using ROW that I could use? Many thanks -- George Gee -- Select cell B35 Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =B35>INDIRECT("$B$37") Click the Format button and select your formatting Click OK, click OK George Gee wrote: > I have this conditional format in cell BB35...

Change a text box to a combo box
Hi there I have created several forms in my database. I realized that I should have made them combo boxes instead of text boxes. Is there any easy way for me to change them into the format I need? Thank you in advance for your help. Right-click on the text box and select changeTo from the menu. -- Reggie "forest8" <forest8@discussions.microsoft.com> wrote in message news:FD867B89-38CC-4F20-A9AE-C177444DA9DA@microsoft.com...the > Hi there > > I have created several forms in my database. > > I realized that I should have made them combo ...

Can I Apply Conditional Formatting to a Chart?
I don't agree with my boss's ideas regarding Excel Charts - I think that they are to cluttered and become less effective. So, I would like to apply conditional formatting via a checkbox to "turn off"(apply white/transparent properties)to one or more series in a chart, effectively eliminating the overlapping series from view temporarily. Can this be done? Is the answer VB?I think this could be very effective in clarifying data that is consistently overlapping each other. (Actually I would just make 3 separate charts on the same page, but nobody is asking me!) Tell me w...

Change Text Case
Anyone know how to change the case (from all caps to lower case, or just first letter cap) without retyping all the text in a worksheet? Thanks! a formula =lower(a1) =proper(a1) -- Don Guillett SalesAid Software donaldb@281.com "Chel" <anonymous@discussions.microsoft.com> wrote in message news:09A9342E-9DED-4BB7-8C08-1DA31E7CEBCB@microsoft.com... > Anyone know how to change the case (from all caps to lower case, or just first letter cap) without retyping all the text in a worksheet? Thanks! Chel, You can use a macro like the following: Sub ConvertCase() Dim Rng As ...

Corporate Name Change #2
How does one change the name of a stock (and symbol) when a company (such as AOL) decides to do so ...

Conditional Formatting #44
How do you get text in cell A1 to appear in a bold font if a dat appears in cell B1? Can this be done in conditional formatting, if so, how -- Message posted from http://www.ExcelForum.com Hi! The following will recognise a _value_ in B1 and make A1 bol accordingly. Select A1 and click Format > Conditional Formatting. Choose Formula is (instead of Cell value is) and put in the bo =$B$1<>"" . This says B2 is not empty. Click Format... and choose Bold. OK. OK. A date is just a number with a particular format. If your need is t spot an entry (rather than a blank) in col ...

Having problems when I assign a background
Good morning, I am working in Visio 2007 (Standard Edition). I have created a document with seven pages (named 'Q 1' thru 'Q 7'). As I would like a background/watermark on all of them I have done the following: Clicked the tab for page 'Q 1' and 'Insert Page'. I named the page 'Draft', and assigned the background page to all the foreground pages through 'Page Setup'. When I finished the order of the pages were messed up, and I can not reorder the pages. They are all locked in position. I tried with another visio document and the same thin...

conditional formula needed
it might be possible to do this as a conditional formula (=if) otherwise a macro might be needed. the thing i'm unsure about is this formula looks at the cells beneath it and keeps looking until a result is returned, i'm not sure if that can be done as a conditional formula. if you really want to know what this is for send me a pm and i'll tell you. if m3 = 0 then "" if l3 = i then "" if m3 = 1 and if l3 = "c" and if i3 > 0 and if e4 – d3 < -.0066 then result = 1 write result in n3 else go to row 5 if e5 – d3 < -.006...

change a word file back to a pdf file
i changed a pdf file in to a word file how can i change it back to a pdf file in microsoft outlook. Also i could not read the file in word Please Help You post Word, PDF question in Outlook group? "Ann" <Ann@discussions.microsoft.com> wrote in message news:0A6AFC3C-2780-4182-BAFC-C07D3D9A7FF3@microsoft.com... >i changed a pdf file in to a word file how can i change it back to a pdf >file > in microsoft outlook. > > Also i could not read the file in word > Please Help Did you change it by simply renaming the file extension? -- Kathleen Orland - MVP...

How do I change all chart colors to black and white?
I am plotting hourly values for a year. I need to have all the data series represented as thin black lines, but EXCEL prefers a huge range of colors and dashed lines along with solid lines. How can I force it to show all the data series the same way, and make them all thin black lines? Double click on one series, choose the format you want, and press OK. Select another series and press the F4 key to repeat, Select and press F4 for each of the other series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/...

Changing view of a new mail message
If you are having problems with the view when composing new messages in Outlook and font is to large try this: Open new message, go to Tab that says format text, then on the menu on the top you will see a zoom button. Click on zoom button and decrease size of zoom. ...

Changing color of icons dynamically
Hi, I need to change the color of icons dynamically, when i try to change the color by following the steps, the background color is getting mismatched. HICON CGenericMFCDlg::CreateGrayscaleIcon(HICON hIcon) { HICON hGrayIcon = NULL; HDC hMainDC = NULL, hMemDC1 = NULL, hMemDC2 = NULL,hMaskDC = NULL; BITMAP bmp; HBITMAP hOldBmp1 = NULL, hOldBmp2 = NULL,hOldBmp3 = NULL; ICONINFO csII, csGrayII; BOOL bRetValue = FALSE; bRetValue = ::GetIconInfo(hIcon, &csII); if (bRetValue == FALSE) return NULL; hMainDC = ::GetDC(NULL); ...