Change Cell colour

[klatlap]

Does anyone know the best solution to do the following.

i have a list of IP addresses like this.

88.113.65.83
88.113.65.83
88.113.65.83
83.245.187.254
88.115.102.139
88.115.102.139
88.115.107.23
88.115.107.23
88.115.107.23
88.115.107.23
88.115.107.143
88.115.107.143

I need to change the cell colour based on the change of the IP address, only need 2 colours, what i need is for the colour to change when the address changes, if the address is the same as the one above it should stay the same colour

 

[Andrew Poulsom]

If your data is in column A starting at row 1 you can use these Conditional Formatting formulas:

=MOD(ROUND(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1

=MOD(ROUND(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=0

Choose the colour you want for each condition.

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] cNo [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]If[/COLOR] Not Intersect(Rng, Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
  [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
     .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        cNo = IIf(n Mod 2 = 0, 34, 35)
        .Add Dn.value, cNo
        Dn.Interior.ColorIndex = cNo
    [COLOR="Navy"]Else[/COLOR]
        Dn.Interior.ColorIndex = .Item(Dn.value)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[klatlap]

Tanks i will give them both a try

 

[klatlap]

With the vba code option where would i change the colour

 

[Andrew Poulsom]

Here?

cNo = IIf(n Mod 2 = 0, 34, 35)