Cell and Column Referencing

[thespian]

Ok, so I'm going to try and explain exactly what I need the cells to do.
Is it possible to get the cells to automatically do the following?

If for example;

Cell L19 = 'A' (The Letter A)
Cell L20 = '2' (The Number 2)

Then in on sheet 2, column A row 2 must be filled in, either (preferably)
a color or the number 1 or letter T, doesn't really matter what it's
filled with so long as it is filled.

If this is at all possible, can it recognize a range of numbers; e.g.

L19 = 'A'
L20 = 5,6,7,8

How do I get it to fill in Sheet 2 Column A Cell 5 and 6 and 7 and 8 with
whatever I can fill it with.

[Jon von der Heyden]

Welcome to the excelguru forums.

I'm struggling to understand what exactly it is that you need, and why? So, in A5:A8, do these cells require manual input by a user or are you just wanting to flag them? I'm wondering if it's a formula solution (perhaps conditional formatting) required, or a VBA sheet change procedure required. If it is a formula solution needed here then inevitably the cell or range of cells you refer to in L19 and L20 will need to be populated with a formula in order to recognise that they are being flagged (unless you are using conditional formatting).

Is there a maximum and minimum range that you might enter in L19 and L20, or might you refer to any cell or range of cells on the grid?

[Ken Puls]

Hi Thespian, and welcome to the forum.

Have a look at the attached and see if that's what you're after.

[thespian]

Hi Ken

That's exactly it! Possible to do?

T

[Ken Puls]

Hi Thespian,

I'm curious if you tried changing the values in L19 and L20? If you do, you'll see that it is actually working in that workbook. (Providing you enable macros.) So yes, totally possible.

I assume that you'll need help making it work in your workbook though. I'd prefer you play with it first though, to make sure it does exactly what you want. Then we can cut and paste.

[thespian]

Sorry blonde moment.

Yes it works perfectly.

[Ken Puls]

Cool.

To use this in your own workbook, what you need to do is this.

• Open your workbook in Excel
• Find sheet that has your L19 & L20 ranges on it
• Right click the sheet's tab and choose "View Code"
• Paste the following in the window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRows() As String
Dim lRow As Long
Dim sCols() As String
Dim lCol As Long
Dim wsTarget
    Select Case Target.Address
        Case Is = "$L$19", "$L$20"
            Debug.Print Target.Address
            'Set target worksheet to mark up here
            Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
            
            'Get array of values
            sCols = Split(ActiveSheet.Range("$L$19"), ",")
            sRows = Split(ActiveSheet.Range("$L$20"), ",")
            
            'Ignore errors (ignores invalid characters)
            On Error Resume Next
            
            'Mark worksheet
            With wsTarget
                For lRow = LBound(sRows) To UBound(sRows)
                    For lCol = LBound(sCols) To UBound(sCols)
                        .Range(sCols(lCol) & sRows(lRow)).Value = 1
                    Next lCol
                Next lRow
            End With
            
        Case Else
            'Do nothing
    End Select
End Sub

• (If you see a line that says Option Explicit, then put it under that line.)
• Adjust the following line to refer to the worksheet you want to paste your stuff to. (Just change the name of the worksheet between the quotes.)

Set wsTarget = ThisWorkbook.Worksheets("Sheet2")

• Go back to the Excel window and test it.
• Save the file once you are happy it's working

If you are using Excel 2007/2010, you will need to save the file in an xlsm file format, as xlsx cannot contain macros.

HTH,