-2

I am a beginner to excel macros.

If I have a table e.g. like this with positions that correspond to a matrix of cells with different positions. How would I create a macro that, based on the position I input into my table (e.g. 1,1:A), would color the corresponding cell of the matrix?

enter image description here

This might be really simple but any help/tips is appreciated.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jakob
  • 1
  • 2
    please see [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and read [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) and don't forget to read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) . Also check out the [tour](https://stackoverflow.com/tour), and ["help center".](https://stackoverflow.com/help/on-topic) – Foxfire And Burns And Burns Jul 06 '22 at 09:53

1 Answers1

0

Here is a macro for that:

Sub test()
Dim cll As Range, txt As String, coordsX As String, coordsY As String
Dim i As Integer, j As Integer

    For Each cll In Range("A1:A14")
        txt = cll.Value
        coordsX = Mid(txt, _
                        WorksheetFunction.Find(":", txt) + 1, _
                        Len(txt) - WorksheetFunction.Find(":", txt) - Len(txt) + WorksheetFunction.Find(",", txt) - 1)
        coordsY = Right(txt, Len(txt) - WorksheetFunction.Find(",", txt))
        
        For i = 1 To WorksheetFunction.CountA(Range("E4:I5"))
            For j = 1 To WorksheetFunction.CountA(Range("D5:D11"))
                If Cells(j + 4, 3).Value = coordsY And Cells(4, i + 3).Value = coordsX Then
                    MsgBox Cells(j + 4, 3).Value & " " & Cells(4, i + 3).Value
                    Cells(j + 4, i + 3).Interior.Color = 500
                End If
            Next j
        Next i
    Next cll
    End Sub

Range A1:A14 is range where those coordinates are (1:1,A...); Range E4:I5 is where table headers are (numbers in this case); Range D5:D11 is where row numbers are (letters in this case);

Interior Color 500 is color code of background. It can be RGB too.

Bes Gh
  • 13
  • 5
  • Thanks! Although would you not have A:1:A15 (1:1,A --> 1:,4,D)., E:4:H4 (1,2,3,4), and D5:D8 (A,B,C,D)? – Jakob Jul 06 '22 at 11:58
  • 'WorksheetFunction.CountA(Range("E4:I5")) ' This code just counts how many rows and columns you have in table; and they must be in table in order to find right cell to highlight. You can change it with numbers, in your example you have 4 rows and colums, so instead of WorksheetFunction.CountA(Range("E4:I5")) you can just wright 4. – Bes Gh Jul 07 '22 at 08:45