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

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


.

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

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