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


New Member
Oct 19, 2009
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.


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

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


New Member
Oct 19, 2009
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
=> Sheet 2
1,2 = 3 times; 1,3 = 3 times; 1,10 = 1 time

Forum statistics

Latest member

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...