I have a workbook with multiple spreadsheets to manage IPs I issue out. One of the worksheets has all the IP addresses I have and the other worksheets are the buildings that the IPs are in. On the worksheet that has all the IPs I want to have it scan the other worksheets and if it sees an IP that is in my main worksheet it colors that cell red.
Frank
assume the master list of IP addresses is in a worksheet named master and the IP addresses are listed in column A. Assume the IP addresses in Master start in cell A2.
Sub MarkinRed()
Dim r As Range, rng1 As Range, rng As Range
Dim v As Variant, i As Long, sh As Worksheet
Dim sAddr As String
With Worksheets("Master")
Set r = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
v = r.Value
For Each sh In Worksheets
If sh.Name <> Worksheets("Master").Name Then For i = LBound(v, 1) To UBound(v, 1) If Len(Trim(v(i, 1))) > 0 Then If Application.CountIf(sh.Cells, v(i, 1)) > 0 Then Set rng = Nothing Set rng1 = Nothing sAddr = "" Set rng = sh.UsedRange.Find(What:=v(i, 1), _ After:=sh.UsedRange(sh.UsedRange.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.UsedRange.FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then rng1.Interior.ColorIndex = 3 End If End If End If End If Next End If
Next End Sub
the above worked for me given the assumptions stated.
Test it on a copy of your workbook
Advertisement