Beginner needs help

phoenixiv

New Member
Joined
Jul 24, 2007
Messages
3
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

Your question is missing

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

phoenixiv

New Member
Joined
Jul 24, 2007
Messages
3
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Yellow : found only in "out of stock"
Blue : Found only in "discontinued"
Red : found in both sheet
Code:
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

New Member
Joined
Jul 24, 2007
Messages
3
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,693
Latest member
BroTr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top