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

 

[mattyn]

I have tried Selection.Interior.Color=xlNone but it turns the selected cells blue!

Any thoughts? I don't want to use white as I want it transparent!

 

[healey21]

Set the color to 0 that should remove any colour like this

.Interior.ColorIndex = 0

 

[mattyn]

Sorted it with Selection.Interiorindex.Color=xlNone

Thank you all for your help!

 

[healey21]

Pleased to read you have a working solution. Thanks for letting me know.

 

[mattyn]

Where I have sorted the shading above, how would I add a letter to each cell that was shaded?

I would like to add for instance:

Red cells - L
Blue cells - D
etc?

 

[L. Howard]

Where I have sorted the shading above, how would I add a letter to each cell that was shaded?

I would like to add for instance:

Red cells - L
Blue cells - D
etc?

You could try this.

Change the 3 for the color from your colorindex code you have and the L to the letter you want.

I assigned the short cut key Ctrl + q to my example for ease of use.
Each user could have a separate short cut key code, where the user would select the cells and hit Ctrl + ?.
You would need to be careful not to use the Reserved Short Cuts that Excel uses, but there are a fair amount available.
Then you enter the short cut below the name for a reminder like '/ Ctrl + q


Howard

Sub Cell_Col_Letter()
'/ Ctrl + q

Dim rngC As Range

For Each rngC In Selection
    rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
        3, xlNone)
    rngC = IIf(rngC = "", "L", "")
Next

End Sub

 

[mattyn]

You could try this.

Change the 3 for the color from your colorindex code you have and the L to the letter you want.

I assigned the short cut key Ctrl + q to my example for ease of use.
Each user could have a separate short cut key code, where the user would select the cells and hit Ctrl + ?.
You would need to be careful not to use the Reserved Short Cuts that Excel uses, but there are a fair amount available.
Then you enter the short cut below the name for a reminder like '/ Ctrl + q


Howard

Sub Cell_Col_Letter()
'/ Ctrl + q

Dim rngC As Range

For Each rngC In Selection
    rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
        3, xlNone)
    rngC = IIf(rngC = "", "L", "")
Next

End Sub

Thank you. I assume I use that instead of the code I use currently on each macro?

 

[L. Howard]

Thank you. I assume I use that instead of the code I use currently on each macro?

Assume you have three users.

I would disregard the use of short cut keys, can be troublesome if mixed with Excel Reserved Short Cut Keys.

Instead assign each user to a small button, or even better, a small SHAPE formatted to the color and a text insert of the letter of the user.

Sub Cell_Col_L() user would have a shape filled red with an L in it.
Sub Cell_Col_M() user would have a shape filled green with an M in it.
Sub Cell_Col_N() user would have a shape filled yellow with an N in it.

Option Explicit

Sub Cell_Col_L()
Dim rngC As Range
For Each rngC In Selection
    rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
        3, xlNone)
    rngC = IIf(rngC = "", "L", "")
Next
End Sub


Sub Cell_Col_M()
Dim rngC As Range
For Each rngC In Selection
    rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
        4, xlNone)
    rngC = IIf(rngC = "", "M", "")
Next
End Sub


Sub Cell_Col_N()
Dim rngC As Range
For Each rngC In Selection
    rngC.Interior.ColorIndex = IIf(rngC.Interior.ColorIndex = xlNone, _
        6, xlNone)
    rngC = IIf(rngC = "", "N", "")
Next
End Sub

Howard

 

[mattyn]

Oh I really like that! I especially like the "re-select" to undo the action!

Thank you - 5 internet pints inbound to you!

You could not help me with my other problem could you please?

http://www.mrexcel.com/forum/excel-questions/850642-data-analysis-exploitation.html