from nothing to more than 1.

[ASadStudent]

Hello everyone, I want to ask how I can change this line from nothing to more than one.
Everytime I try it doesn't seem to work.

 If Not rng Is Nothing Then rng.Interior.Color = vbRed

Thanks a lot for helping me

(Here is the line of code of code where it is used in)

 Sub Kijken_2()
  Dim omzet As Worksheet, Maandafsluiting As Worksheet
  Dim data As Variant, ky As Variant
  Dim lr As Long, rw As Long
  Dim d As Object, d2 As Object
  Dim rng As Range
 
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
 
  Set omzet = Workbooks.Item("Omzet").Sheets("Sheet1")
  Set Maandafsluiting = Workbooks.Item("Maandafsluiting").Sheets(1)
 
  lr = omzet.Cells(Rows.Count, 2).End(3).Row
  With omzet.Cells(1, 1).Resize(lr, 14)
    data = .Value
    .Interior.ColorIndex = xlNone
  End With
 
  For rw = LBound(data) To UBound(data)
    If data(rw, 14) <> 0 Then
      ky = data(rw, 2)
      If Not d.exists(ky) Then
        d(ky) = data(rw, 14) & "|" & rw
      End If
    End If
  Next rw
 
  lr = Maandafsluiting.Cells(Rows.Count, 2).End(3).Row
  data = Maandafsluiting.Cells(1, 1).Resize(lr, 6).Formula
  
  For rw = LBound(data) To UBound(data)
    ky = data(rw, 2)
    d2(ky) = Empty
    If d.exists(ky) Then
      data(rw, 6) = Split(d(ky), "|")(0)
    End If
  Next rw

  For Each ky In d.keys
    If Not d2.exists(ky) Then
      rw = Split(d(ky), "|")(1)
      If rng Is Nothing Then
        Set rng = omzet.Cells(rw, 2)
      Else
        Set rng = Union(rng, omzet.Cells(rw, 2))
      End If
    End If
  Next
 
  If Not rng Is Nothing Then rng.Interior.Color = vbRed
 
 
  Maandafsluiting.Cells(1, 6).Resize(UBound(data)).Formula = Application.Index(data, 0, 6)
End Sub

 

[HaHoBe]

Hi ASadStudent,

the code checks if the range has been assigned or not by checking for If Not rng Is Nothing Then and if the rng exists work with it - I won't see any benefit in using something like

If rng.Cells.Count > 0 Then

Ciao,
Holger

 

[ASadStudent]

Hi ASadStudent,

the code checks if the range has been assigned or not by checking for If Not rng Is Nothing Then and if the rng exists work with it - I won't see any benefit in using something like

If rng.Cells.Count > 0 Then

Ciao,
Holger

Thanks for the suggestion.
This line of code is as a check to see if the product number in the first excel file is found in the second one.

If it isn't found then I want it to turn red and if it is found multiple times then I want it to turn orange.

So do you know how I can change this line so that I can also do the second check ?

 

[bebo021999]

Try to declare rng as variant
Replace:

Dim rng As Range

by

Dim rng

 

[Gokhan Aycan]

Can't say for sure for your case, but depending, checking for a comma inside rng.Address may help.

Another approach would be keeping a counter where you union found ranges. If counter > 1 then multiple ranges were found. You may even use a single boolean you set to true in the same place.

Another Edit: If rng is only setting a single cell, then HaHoBe's if statement would work just make it greater than 1.

 

[kvsrinivasamurthy]

Try

If Not Rng Is Nothing Then
Rng.Interior.Color = vbRed
ElseIf Rng.Cells.Count = 1 Then
Rng.Interior.Color = "as required"
ElseIf Rng.Cells.Count = 2 Then
Rng.Interior.Color = "as required"
End If