painted a cell pressing button VBA EXCEL

Question

I have a question and I will post a image below to better understand:

The Button 2 will paint cells. But I want whatever cell value is in J4, it will be cell that going to be painted. In this case, A2 will be painted.

I want to make the Button 2 paint a cell when clicked, but the reference of that cell is given by the value type in the J4 cell. Whenever I change the value of cell J4, when I press Button 2 will paint that value in the J4 reference. I couldn't figure it out how to make Button 2 recognize that the value in J4 is actually a cell reference.

Answer 1

try this, so u can choice with inputbox and u can according to your desired color

Sub colourrange()
On Error Resume Next 

        Title = "Choice Cell"
        Set rngA = Application.Selection
        Set rngA = Application.InputBox("Select Range:", Title, rngA.Address, Type:=8)
    
        rngA.Interior.Color = vbRed
    
End Sub

*If there’s any unclear information please let me know

Answer 2

assuming the worksheet button

• is a form control

• is called after "Button 2"

• is assigned a "Button2_Click()" macro placed in the code pane of the sheet where it resides (right click on the sheet name and choose "View Code" to access it)

then you can use the following code:

Sub Button2_Click()
    Range(Range("J4").Value2).Interior.Color = vbYellow
End Sub

Should you need/want to assign the button a macro residing in a standard module, than you have to use a code as per @TimWilliams comment