Shading Selected Cells

[mattyn]

Good Day All

New to the forum, but relatively competenent with the basics of Excel. I will be asking a lot of questions as I try to expand my knowledge, so i thank you all in advance.

First one: A worksheet that acts as a personnel management whereabouts tool. I want to be able to selct a few cells, and then click a button with an associated macro so that those selected cells are then shaded a particular colour.

For instance, I select A15:G15 with the mouse, then click a button to shade it green.

Hope this makes sense? Is it possible?

Cheers

Matt

 

[healey21]

Welcome to the forum, Try this out.

Sub CellCol()
'Highlight the cells then this will colour them
Selection.Interior.Color = vbGreen
End Sub

 

[Special-K99]

Just make the selection then in the "ribbon" at the top of the screen click the tin of paint below "PAGE LAYOUT" to fill the background or the coloured "A" to colour the text. Use the dropdowns to the right of the icons to select other colours

 

[mattyn]

Just make the selection then in the "ribbon" at the top of the screen click the tin of paint below "PAGE LAYOUT" to fill the background or the coloured "A" to colour the text. Use the dropdowns to the right of the icons to select other colours

Thank you for the reply. This is the current method but falls over as the S/S is used by a number of people, all with differing colour perception and most without the same sense of OCD as I do - there is a lot of "That'll do" here, which infuriates me.

 

[healey21]

Have you tried my code sample!

 

[mattyn]

Have you tried my code sample!

Not yet - Where do I put that? Right click on the tab, view code?

 

[healey21]

Add this on a module via VBA so use Alt + F11 on the keyboard then Insert menu and module, paste the code in.

When the user wants to do it they can run the macro (Alt + F8) in excel user screen or attach the macro to a button.

 

[mattyn]

Add this on a module via VBA so use Alt + F11 on the keyboard then Insert menu and module, paste the code in.

When the user wants to do it they can run the macro (Alt + F8) in excel user screen or attach the macro to a button.

That is great - sorted it. Now how to I do other colours to be assigned to different buttons?

Greta help, thank you!

 

[healey21]

Highlight VBGreen and Press F1 (Help) this will show you the VB Colours you can use now if that is Ok then that's sorted, if you need other colours run this bit of code to check out the colours available and then you can add a number to each cell in column B this will give you the numbers against the colour.

sub displayColour()
Sheets.Add
dim I as Long
For I = 1 to 56
cells(n,1).Interior.ColorIndex = I
next I
End sub

 

[mattyn]

Works brilliantly! If I wanted no colour, not white, is that easy?