What do these mean: "Select case" and "target.offset"?

bovinda

Board Regular
Joined
Jun 11, 2005
Messages
87
I have a script I can't seem to get to work that is supposed to change font color. It worked in Excel 2003, but I updated the spreadsheet for 2007 and now it's not functional.

I feel like the problem has to do with these commands, which are part of the script. Could anyone tell me in simple language what they mean, and how they are used?

I have one like in the script where they're used together: "Select case target.offset (0, 1)" and I cannot for the life of me figure out what it means, for some reason.

Or what does "select case target" and "select case target.column" mean? Knowing this might help me fix this script.

I greatly appreciate any and all suggestions!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
When you use a change event, Excel sets the cell as a range named Target. So

Select Case Target.Offset(0,1) means look at the value in the cell to the Right of the Target

Select Case Target.Column means look at the column number of the Target

Select Case Target simply means look at the Target Value

Why don't you post your code and we can explain further

lenze
 

bovinda

Board Regular
Joined
Jun 11, 2005
Messages
87
Hi Lenze, I think those aren't the problem then; thanks for the good explanation. This is actually code that you helped me write more than a year ago! (It worked great, but I can't seem to get it to work now for a newer version of my spreadsheet.)

It's just supposed to change font color of adjacent cells depending on pre-determined data in a pair of given cells.

Here's the code in its entirety:

Code:
Private Sub FontColorChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column < 8 Then Exit Sub
    If Target.Column > 9 Then Exit Sub
    Dim myColor As Variant
    Select Case Target.Column
        Case Is = 9
        Select Case Target
            Case Is = "Util, Gas", "Util, Phone", "Util, Power", "Bills, FAP, Jeff", "Bills, FAP, Pam", "Bills, Other": myColor = 18
            Case Else
                Select Case Target.Offset(0, -1)
                Case Is = "Deb EO", "Deb WF", "ATM Withdrawal, EO", "ATM Withdrawal, WF": myColor = 55
                Case Is = "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam": myColor = 47
                Case Is = "Work, Jeff": myColor = 5
                Case Is = "Work, Pam": myColor = 13
                Case Is = "Dep, Other": myColor = 31
                Case Is = "CC Pay WF", "CC Pay AI", "CC Pay HSBC", "CC Pay AmEx", "CC Pay Blank 1", "CC Pay Blank 2", "CC Pay Exp", "CC Pay VS", "CC Pay Corp 1", "CC Pay Corp 2", "CC Pay Corp 3": myColor = 9
                Case Is = "Chk --> Sav", "Sav --> Chk", "Dep --> Sav", "Sav Withdrawal": myColor = 10
                Case Else: myColor = xlAutomatic
                End Select
        End Select
        Case Is = 8
        Select Case Target.Offset(0, 1)
            Case Is = "Util, Gas", "Util, Phone", "Util, Power", "Bills, FAP, Jeff", "Bills, FAP, Pam", "Bills, Other": myColor = 18
            Case Else
                Select Case Target
                Case Is = "Deb EO", "Deb WF", "ATM Withdrawal, EO", "ATM Withdrawal, WF": myColor = 55
                Case Is = "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam": myColor = 47
                Case Is = "Work, Jeff": myColor = 5
                Case Is = "Work, Pam": myColor = 13
                Case Is = "Dep, Other": myColor = 31
                Case Is = "CC Pay WF", "CC Pay AI", "CC Pay HSBC", "CC Pay AmEx", "CC Pay Blank 1", "CC Pay Blank 2", "CC Pay Exp", "CC Pay VS", "CC Pay Corp 1", "CC Pay Corp 2", "CC Pay Corp 3": myColor = 9
                Case Is = "Chk --> Sav", "Sav --> Chk", "Dep --> Sav", "Sav Withdrawal": myColor = 10
                Case Else: myColor = xlAutomatic
                End Select
        End Select
    End Select
    Me.Cells(Target.Row, "B").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "D").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "G").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "H").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "K").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "M").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "N").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "O").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "P").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "Q").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "R").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "S").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "T").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "U").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "V").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "W").Font.ColorIndex = myColor

End Sub

I'm not sure about: (1) how to reinsert it into my new spreadsheet (record macro? make new module under macro menu?). (2) I tried copying and pasting it into a "Module" and debugging, and it says there's an error in the "Me" statements towards the end.

I greatly, greatly appreciate any suggestions you, or anyone else, may have! Thanks Lenze,

Jeff
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
I will look at this more closely later, but your code needs to be in the WorkSheet module (Right Click on the sheet tab and choose "View Code") and must be named
Code:
Private Sub WorkSheet_Change(ByVal Target as Range)

HTH
lenze
 

bovinda

Board Regular
Joined
Jun 11, 2005
Messages
87

ADVERTISEMENT

Hi Lenze, so copied the code to the worksheet after R-clicking on "View Code". I also changed the name up top to "Private Sub WorkSheet_Change(ByVal Target As Range)".

It's still not working yet. What else am I not doing right? Or is there something I just need to change in the code? I don't get any errors when I compile it now, but nothing happens.

Thanks for your help so far!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Don't know. Are you sure Events are Enabled? Open the VBE(ALT+F11). Choose View Imediate Window. Enter
?Application.EnableEvents
Does it return True or False? If False, enter
Application.EnableEvents = True

Follow up later.:p
lenze
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

What are you doing to trigger this code running?
 

bovinda

Board Regular
Joined
Jun 11, 2005
Messages
87
Lenze, thanks buddy, you helped me figure it out (once again)!

When I put in "?Application.EnableEvents", it wouldn't let me run it, saying macros were disabled. I went ahead and enabled all macros and restarted Excel, and it seems to be working so far! Thank you so much (again) for your help! :) I think I can fix it so it affets the cells I want affected now--if not I'll post back, but it looks like it's working perfectly now!

I have a couple residual, though not so important, questions for my own edification, to any who care to answer:

1. Why did the name have to be changed to "Private Sub WorkSheet_Change(ByVal Target As Range)" and what does that even mean?

2. Is it safe to enable macros globally? (I mean, is there any other way to do it?) As long as you're careful, should be no big risk, right?

3. What is the difference in running this macro from the worksheet code versus how I tried to do it originally, which I guess was from the workbook itself?

And Rorya, thanks for checking to help too! This code is set up so it is triggered any time cells 8 or 9 change, I believe...
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
1. WorkSheet Change is 1 of many Event procedures. Event procedures are in 3 categories. Application Level, WorkBook level and WorkSheet level. WorkSheet Change is a WorkSheet level procedure. Because it goes in a sheet module, it must be named to reflect the procedure desired. Excel will not recognize any name not resident to Excel. Chip Pearson has a good discussion on procedures
http://www.cpearson.com/excel/Events.aspx

2. Yes. The dangers of macros has been GREATLY over hyped. Since you probably are only using files you are a co-worker created, there should be no danger. I would, however, be careful about any file from unknown scources

3. See answer 1. Because it is a WorkSheet Event, it must reside in the WorkSheet module.

Good luck on your project

lenze
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,547
Members
417,151
Latest member
ChickenTenderer

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