Need macro to run fully in 1 sheet but partially in another

[zoso]

Hi there!

I have the following code assigned to a toolbar button:

Sub FillCell() 
With Selection 
.Font.Name = "Wingdings" 
.Font.Size = 8 
.Font.ColorIndex = 15 
.Value = "¤" 
With Selection.Interior 
.ColorIndex = 36 
.Pattern = xlSolid 
End With 
End With 
End Sub

What I'm after is an amendment to this code so that it runs as above only in sheet 'Training Log' column F.

However, I also need the following 2 criteria to apply:

1) If I select a cell in 'Analysis' sheet column F I need the code to run but without the font part i.e. only the shading is applied.

2) If any other column or sheet is selected I don't want the code to run at all.

Hope this is possible - and thanks!

 

[BBHRVS]

Hi Zoso

Please put this in the worksheet Analysis module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then fillcell 1
End Sub

And this in the workshhet Training log module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then fillcell 2
End Sub

And this in a standard module:
Sub fillcell(arg)
'arg=1 macro launched from analysis sheet - arg=2 macro launched from training log sheet
If arg = 2 Then
With Selection
.Font.Name = "Wingdings"
.Font.Size = 8
.Font.ColorIndex = 3
.Value = "¤"
End With
End If
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End Sub

Should work...

 

[zoso]

Hi there!

Many thanks for your help - that works 99% fine - just a couple of things:

1) How do I get this to run from a toolbar button once the cell is selected, as I don't want this to run just by selecting the cell in the column (apologies for the confusion)

2) I have 2 other colours that I want to do the same thing with - colorindex 6 and 8 - that I have 2 other buttons for, and i need the same thing to apply to those - hence the cell selection but firing the macro from the button.

Thanks again!

 

[BBHRVS]

I am suddenly wondering if you want to keep your button. The herebelow applies for automatic macro.
If you want to work through buttons, you will need to create 2 buttons, one in each wsht.
The one in analysis will launch the following.
sub macro1
If activecell.Column = 6 Then fillcell 1
End sub
The one in training log
sub macro1
If activecell.Column = 6 Then fillcell 2
End sub
The third one being not changed. All macros in normal module.
BRgds;

 

[zoso]

Hi again - hmmm, was hoping there was some way of doing this using 3, not 6 different buttons (just 1 for each colour/sheet, not 2)...but thanks for your help anyway!

 

[BBHRVS]

My answer crossed over with your new question !
From a toolbar, you will only one button, right ?
Then the macro linked to this button should be
sub myformat()
If activesheet.name="Analysis" and activecell.column=6 then
fillcell 1
ElseIf activesheet.name="Training Log" and activecell.column=6 then
fillcell 2
Else
Msgbox "This macro does not apply for these Wsht/cells"
End if
End sub

Strictly the same for others wshts, colors, just changing the name of the whsts and the colorindex in the macro corresponding to fillcell (just create new ones, this is simpler!)
Brgds;

 

[zoso]

Hi again - thanks for sticking with me on this!

Can I just clarify?

The 2 worksheets are always the same. I just want to shade the cell with 1 of the 3 specified colours. Each of the 3 buttons on the toolbar should relate to one of the colours.

How do I therefore amend your code for the other 2 colours with everything else being the same?

Thanks again!

 

[BBHRVS]

hi;

Button 1 linked macro:
sub myformat1()
If activesheet.name="Analysis" and activecell.column=6 then
fillcell 1,36
ElseIf activesheet.name="Training Log" and activecell.column=6 then
fillcell 2,36
Else
Msgbox "This macro does not apply for these Wsht/cells"
End if
End sub

Button 2 linked macro:
sub myformat2()
If activesheet.name="Analysis" and activecell.column=6 then
fillcell 1,6
ElseIf activesheet.name="Training Log" and activecell.column=6 then
fillcell 2,6
Else
Msgbox "This macro does not apply for these Wsht/cells"
End if
End sub

Button 3 linked macro:
sub myformat3()
If activesheet.name="Analysis" and activecell.column=6 then
fillcell 1,8
ElseIf activesheet.name="Training Log" and activecell.column=6 then
fillcell 2,8
Else
Msgbox "This macro does not apply for these Wsht/cells"
End if
End sub

Last macro:
sub fillcell(wsht,color)
'wsht=1 macro launched from analysis sheet
'wsht=2 macro launched from training log sheet

If wsht = 2 Then
With Selection
.Font.Name = "Wingdings"
.Font.Size = 8
.Font.ColorIndex = 3
.Value = "¤"
End With
End If

With Selection.Interior
.ColorIndex = color
.Pattern = xlSolid
End With
End Sub

BRgds.

 

[zoso]

FANTASTIC - YOU DID IT! IT WORKS PERFECTLY!

THANK YOU VERY MUCH!