VBA: Using two values to create an intersection

Question

Hello everyone,

I have two separate tables, one contains location information and the current quarter in a fixed position.

Location 1	Q1

I would like to run a macro, which takes the above mentioned information and marks the intersecting cell in color in a different worksheet.

Location	Q1	Q2	Q3	Q4
Location 1	Green
Location 2
Location 3
Location 4

Many thanks

Answer 1

Hi Thomas,

yes it does. But I also would like to add that over time (ideally) all cells get filled with the incoming responses.

I will ask post the question to Stack Overflow.

Many thanks,

Answer 2

Hi, if a code generate something like second picture from the first one, does that meet your needs.

Sub SqlTest()

    strConnection = _

        "Provider=Microsoft.ACE.OLEDB.12.0;" & _

        "User ID=Admin;" & _

        "Data Source='" & ThisWorkbook.FullName & "';" & _

        "Mode=Read;" & _

        "Extended Properties=""Excel 12.0 Macro;"";"

    strQuery = _

        "TRANSFORM COUNT(*)-100 SELECT [Location] FROM [Sheet1$] GROUP BY [Location] PIVOT QUARTER"

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.Open strConnection

    Set objRecordSet = objConnection.Execute(strQuery)

    RecordSetToWorksheet ActiveSheet, objRecordSet

    objConnection.Close

    For Each cell In Range("A1").CurrentRegion.Cells

    If cell.Value = -99 Then cell.ClearContents: cell.Interior.ColorIndex = 10

    Next

End Sub

Sub RecordSetToWorksheet(objSheet As Worksheet, objRecordSet As Object)

    With objSheet

        .Cells.Delete

        For i = 1 To objRecordSet.Fields.Count

            .Cells(1, i).Value = objRecordSet.Fields(i - 1).Name

        Next

        .Cells(2, 1).CopyFromRecordset objRecordSet

        .Cells.Columns.AutoFit

    End With

End Sub

Note that VBA is no longer supported. It's recommended to post VBA programming questions to Stack Overflow by using the vba tag, along with any other relevant tags.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Answer 3

Hi Thomas,

many thanks for the answer. However, I would need it as VBA as I intend it to use the table as a checklist at the end of a work routine. So after a particular sheet has been corrected, the user should click a button and the intersecting cell should be colored.

Ideally, I don't store the location/quarter info permanently and can just use them once to color the required cell as the first sheet changes frequently to work on different locations.

Again many thanks for your help.

Answer 4

Hi CarliniBO,

Welcome to Microsoft Community. You can accomplish it without VBA, and conditional formatting is enough.

=XLOOKUP($E16,$A$16:$A$19,$B$16:$B$19,FALSE,0)=F$15

Best Regards,

Thomas.L - MSFT | Microsoft Community Support Specialist