Compare two numbers and fill third cell and one more which I dont know

Nikkei

New Member
Joined
Oct 19, 2009
Messages
2
First off, I am glad to be here.Hopefully I wil get some good advice. Sorry if I seem a bit noobish It's unintentional.

Ok, so, I need to make in excel the following.
I have in one sheet 8 columns with random numbers from 1 to 50 and about 300 rows.
In another sheet I have the numbers 1-50 in the top row and all the 300 rows as in the first sheet.
I want to be able to fill the cells with black on each cell where the number is in the first sheet.
So instead of having eight columns with values as in the first sheet I will have eight black boxes per row with the value at the top.

And lastly:
I want to establish how many times did the numbers 1 and 2, 1 and 3 etc. found themselves in the same row and put that value in a separate sheet.

That's about it I guess, I really don't know if this can be made either with formulas or VBA (either way formulas I know a bit but VBA no realy)

Thanks in advance.
 

Some videos you may like

Excel Facts

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

datadata

New Member
Joined
Oct 15, 2009
Messages
39
This can blacken the cells. I'm not really sure what you're asking for in the last part of your post.

Code:
Option Explicit

Sub BlackenRows()
    
    Dim iCtr As Integer
    Dim iSubCtr As Integer
    Dim iVal As Integer
    
    For iCtr = 1 To Worksheets(1).Range("A1").SpecialCells(xlLastCell).Rows
        For iSubCtr = 1 To Worksheets(1).Range("A1").SpecialCells(xlLastCell).Columns
            
            iVal = Worksheets(1).Cells(iCtr, iSubCtr).Value
            
            Worksheets(2).Cells(1, iVal).Interior.ColorIndex = 1
            
        Next iSubCtr
    Next iCtr
        
End Sub
 

Nikkei

New Member
Joined
Oct 19, 2009
Messages
2
Thanks for your reply, I tried your script but it only colors the A1 in the second sheet and as for my second request it goes something like this
Sheet 1
1,2,3,4,5,6,7,8
9,2,3,1,4,5,7,6
6,7,1,2,3,8,5,6,10
etc.
=> Sheet 2
1,2 = 3 times; 1,3 = 3 times; 1,10 = 1 time
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,623
Members
412,333
Latest member
hectamuptra
Top