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

#### bovinda

##### Board Regular
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### lenze

##### Legend
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
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
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

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

lenze

#### RoryA

##### MrExcel MVP, Moderator

What are you doing to trigger this code running?

#### bovinda

##### Board Regular
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
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.

lenze

Replies
6
Views
310
Replies
1
Views
191
Replies
2
Views
250
Replies
5
Views
819
Replies
1
Views
235

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,649
Messages
5,832,879
Members
430,175
Latest member
Sheenamarie

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

### Which adblocker are you using?

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

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