VBA code colouring cells as i click on them

[kanelones]

Hi guys, need a code to replicate what excel has on its own ribbon. i mean, need to asign a button in order to paint any cell i choose to , for example, yellow.

Tried this: ( not the result i looking for of course)

CAn you help me?

Tks!

Sub Macro3()
'
' Macro3 Macro
'


'




ActiveCell.Range(" my choose- one or many cells as i want ").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

 

[Michael M]

Do you want it to stay colored or only colored while it is active ???

 

[harveya915]

I asked for help on a similar issue. I will post the link to the thread on this site. I would tell you how to specifically write your code but I know practically nothing about VBA. Hopefully just disecting the code that was provided to me, you might be able to adjust it to your needs. Page 3 is where it talks about this. Hope it helps.

https://www.mrexcel.com/forum/excel-questions/1050409-insert-checkbox-using-button-3.html

 

[My Aswer Is This]

Try this:
The active cell interior color will be yellow
But now this script will remove any cells interior color already set.
So all cells on the active sheet will have no interior color except for the active cell which will be Yellow.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  5/3/2019  10:20:39 PM  EDT
Cells.Interior.ColorIndex = xlNone
Target.Interior.Color = vbYellow
End Sub

 

[My Aswer Is This]

Here is another option:
Any cell with a value will be colored Green
The active cell will be colored Yellow.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  5/3/2019  10:28:39 PM  EDT
Dim r As Range
Cells.Interior.ColorIndex = xlNone
For Each r In ActiveSheet.UsedRange
    If r.Value <> "" Then r.Interior.Color = vbGreen
Next
Target.Interior.Color = vbYellow
End Sub

 

[kanelones]

Nice, but not what i'm looking for...

Sorry i did not make myself clear, i just need to select some cells, and click on a button in order to paint them. Emulating the same function that we have on our excel ribbon, but using a button somewhere else on my sheet...
Just wondering if i can do that, it would help me on my daily job, reconciliating . Maybe is any way to do that..

The title i wrote on my thread was not fully understandable.


Thanks guys

 

[kanelones]

OK, i think i found it based on your code



Sub color()


Dim r As range
Cells.Interior.ColorIndex = xlNone
For Each r In ActiveSheet.UsedRange
If r.Value <> "" Then r.Interior.color = vbGreen
Next




End Sub

Thank you!

 

[kanelones]

This is the final one!

Just asigned these codes to my buttons and that's it!

Thank you guys, you help me think



'paint selected cells

Private Sub CommandButton1_Click()
Selection.Interior.ColorIndex = 50
Selection.Locked = True
Selection.FormulaHidden = False
End Sub






' erase color
Private Sub CommandButton2_Click()


Selection.Interior.ColorIndex = 0
Selection.Locked = True
Selection.FormulaHidden = False






End Sub