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.
 

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
 

Forum statistics

Threads
1,082,119
Messages
5,363,275
Members
400,724
Latest member
Angeljurich15

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top