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

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Hi there!

I have the following code assigned to a toolbar button:
Code:
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

BBHRVS

Board Regular
Joined
Nov 3, 2005
Messages
94
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

Well-known Member
Joined
Oct 23, 2003
Messages
725
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

Board Regular
Joined
Nov 3, 2005
Messages
94
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

Well-known Member
Joined
Oct 23, 2003
Messages
725

ADVERTISEMENT

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

Board Regular
Joined
Nov 3, 2005
Messages
94
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

Well-known Member
Joined
Oct 23, 2003
Messages
725

ADVERTISEMENT

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

Board Regular
Joined
Nov 3, 2005
Messages
94
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

Well-known Member
Joined
Oct 23, 2003
Messages
725
FANTASTIC - YOU DID IT! IT WORKS PERFECTLY!

THANK YOU VERY MUCH!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top