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

[bovinda]

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!

 

[lenze]

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]

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:

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]

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

Private Sub WorkSheet_Change(ByVal Target as Range)

HTH
lenze

 

[bovinda]

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]

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.
lenze

 

[RoryA]

What are you doing to trigger this code running?

 

[bovinda]

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]

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