Beginner needs help

[phoenixiv]

Hello,

What I'm trying to do is compare three coloums in three diffrent worksheets and print the data to a forth worksheet. Worksheet 1 is a main database of products worksheet 2 is a list of out of stock items, and sheet 3 is discontinues items. I barrowed a small piece of code from an example I did find and modified it, but not sure were to go from there. This is the code I have thus far. The worksheets will vary in the number of items so I've set each range to the maxium possible, at least as I understand things. If anyone can point me in the right direction, I would be greatful.

Sub Test()
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim c As Range
Dim x As Long
With Worksheets("maindb")
Set Rng1 = .Range("F2:F" & .Range("F65536").End(xlUp).Row)
End With
With Worksheets("Out of Stock")
Set Rng2 = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With
With Worksheets("Discontinued")
Set Rng3 = .Range("A1:A" & .Range("A65536").End(x1Up).Row)
End Sub

 

[jindon]

Hi

Your question is missing

1) how to compare those 3 columns?
2) how should the result look like?

 

[phoenixiv]

Thanks for the reply.

ok, perhaps this will help to clairify. I have databse of 3500 products in the maindb worksheet, in colum F is a list of product numbers, these same numbes also appear in the out of stock and discontinues worksheets, for items that meet either condition. What I need is to compare the maindb with the other worksheets to mark which items are out of stock and which have been discontinued. The perfered format for the results would be a color coded list blue for out of stock and red for discontinued items.

 

[jindon]

try
Yellow : found only in "out of stock"
Blue : Found only in "discontinued"
Red : found in both sheet

Sub test()
Dim a, i As Long, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("maindb")
     With .Range("f1",.Range("f" & Rows.Count).End(xlUp))
          .Interior.ColorIndex = xlNone
          a = .Value
     End With
     For i = 2 To UBound(a,1)
          If Not dic.exists(a(i,1)) Then dic.add a(i,1), "f" & i
     Next
     With Sheets("out of stock")
          a = .Range("a1",.Range("a" & Rows.Count).End(xlUp)).Value
     End With
     For Each e In a
          If dic.exists(e) Then Range(dic(e)).Interior.Color = vbYellow
     Next
     With Sheets("discontinued")
          a = .Range("a1",.Range("a" & Rows.Count).End(xlUp)).Value
     End With
     For Each e In a
          If dic.exists(e) Then
               If .Range(dic(e)).Interior.Color = vbYellow Then
                    .Range(dic(e)).Interior.Color = vbRed
               Else
                    .Range(dic(e)).Interior.Color = vbBlue
               End If
          End If
     Next
End With
End Sub

And I think you could do this with Conditional Format
Edited: code

 

[phoenixiv]

Wow. Thank you so much, that works great. Not sure what you mean exactly by conditional format, if you'd like to elaberate though. Thanks Again.

 

[jindon]

Well I'm not good at formulas though

1) Select F2:F of last row
2) Go To [Format] - [ConditionalFormatting]
3) Change 1st dropdown to "FormulaIs"
4) enter =And(CountIf('out of stock'!a:a,f2)>0,CountIf(discontinued!a:a,f2)>0)
5) choose the cell color, Red

6) click [Add]
7) Change 1st dropdown to "FormulaIs"
8) enter =CountIf('out of stock'!a:a,f2)>0
9) choose the cell color, Yellow

10) click [Add]
11) change to "FormulaIs"
12) =CountIf(discontinued!a:a,f2)>0
13) choose the cell color, Blue

HTH