If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel Conditional Formatting?
Hi,
I have our company IP address range in excel format. I want to do conditional formatting so I clearly see the different scopes. If I do conditional formatting so that 'If Cell', 'Is Between' '192.168.61.0' & '192.168.61.255' format colour as Red. Only a few of the IPs actually go red! i.e. 192.168.61.25 is red, but 192.168.61.27 isn't! Can anyone see why? Is this a bug? Cheers Ben |
#2
|
|||
|
|||
Excel Conditional Formatting?
Hi,
I think that the ip address is being treated as text. in the examples you give, (looking at just the .25 and .27 sections) .25 is "less than" .255 alphabetically, but .27 is "greater than" .255. Suggestion: use text to columns with "a period" as the delimiter. Perform your conditional formatting on the original column using the "Formula Is" on cell containing the final ip section. You can then hide the extra columns created. John. -----Original Message----- Hi, I have our company IP address range in excel format. I want to do conditional formatting so I clearly see the different scopes. If I do conditional formatting so that 'If Cell', 'Is Between' '192.168.61.0' & '192.168.61.255' format colour as Red. Only a few of the IPs actually go red! i.e. 192.168.61.25 is red, but 192.168.61.27 isn't! Can anyone see why? Is this a bug? Cheers Ben . |
#3
|
|||
|
|||
Excel Conditional Formatting?
Try formula is and something like
=AND(MID(B1,12,255)+0=0,MID(B1,12,255)+0=255) post back if you need to format according anything else but the last characters -- Regards, Peo Sjoblom "Ben Blackmore" wrote in message .. . Hi, I have our company IP address range in excel format. I want to do conditional formatting so I clearly see the different scopes. If I do conditional formatting so that 'If Cell', 'Is Between' '192.168.61.0' & '192.168.61.255' format colour as Red. Only a few of the IPs actually go red! i.e. 192.168.61.25 is red, but 192.168.61.27 isn't! Can anyone see why? Is this a bug? Cheers Ben |
#4
|
|||
|
|||
Excel Conditional Formatting?
Hi Ben,
See Sorting TCP/IP Addresses, and the like http://www.mvps.org/dmcritchie/excel/sorttcp.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ben Blackmore" wrote in message .. . Hi, I have our company IP address range in excel format. I want to do conditional formatting so I clearly see the different scopes. If I do conditional formatting so that 'If Cell', 'Is Between' '192.168.61.0' & '192.168.61.255' format colour as Red. Only a few of the IPs actually go red! i.e. 192.168.61.25 is red, but 192.168.61.27 isn't! Can anyone see why? Is this a bug? Cheers Ben |
Thread Tools | |
Display Modes | |
|
|