Formatting a cell by clicking on the cell

[gminocha]

Hello everyone!!


I am interested in making up an attendance register on excel.


Please advise if the formatting of the cell (change of background colour) can be done by just clicking on it.


I have tried changing the background colour of the cells in VB by:

'With Selection.Interior

.ColorIndex = 26

.Pattern = xlSolid

End With'


but the issue here is attaching the macro or running the macro on every cell which turn the background color when clicked.


Regards

Gaurav

 

[Luke M]

This is what you asked for...but do you need some way of turning the cell back to original color? To install, right click on sheet tab, view code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo EscapeClause

With Target.Interior

.ColorIndex = 26

.Pattern = xlSolid

End With

EscapeClause:

'Something went wrong, escape!

End Sub



If you only need a specific set of cells changed, you could insert a line like this at the begeinning:



If Intersect(Target,Range("C2:C100")) is Nothing then Exit Sub

 

[gminocha]

Thanks a lot!!


It does work as advised and i also require to revert back to original colour if the same cell is clicked again.


I am not fully familiar with the command sets and excel formulas and have not understood how it worked but i appreciate your prompt support and help.


You guys rock !!


Regards

Gaurav

 

[Luke M]

You're very welcome. To change back, we can put an If statement in the see if the color has already been changed.

A switching back and forth macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo EscapeClause

With Target.Interior


If .ColorIndex = 26 Then

.ColorIndex = xlNone 'Some other color?

Else

.ColorIndex = 26

End If


.Pattern = xlSolid

End With

EscapeClause:

'Something went wrong, escape!

End Sub

 

[gminocha]

Thanks a lot again!!!


Grateful for your help. I could not have done it myself without spending atleast a week breaking my head.


Hats off your website and everyone associated with the creation and running of this excellent source of information.


Regards

Gaurav