Help with VBA script for changing font color in a cell

[bovinda]

I am trying to figure out how to change the font color of a cell depending on the data in nearby cells. I'm sure the only way to do this is with a VBA script of some kind, but the problem is that I am a moron at this. So any help/suggestions/advice is greatly appreciated!

Basically, I would want the color in columns B and D to vary depending on what cells in column E contain.

For example:

1. If E10 says "Debit" or "ATM Withdrawal" then B10 and D10 would change to indigo.

2. If E10 says "Dep Fi-Aid, Jeff" or "Dep Fi-Aid, Pam" or "Dep, Other" then B10 and D10 would change to blue grey.

3. If E10 says "Work, Jeff" then B10 and D10 would change to violet.

I have about seven different colors all together, depending on what is in E10. If it is anything other than a specified text (say I typed "Cash" or anything else instead of the examples above), I would just want it to stay black (or change back to black if the recognized text in E10 was deleted or typed over with non-recognized text).

I hope this makes sense. Is there some way to do this? I think I already have a VBA script in the file, so is there some way to add this in to it? I really don't know anything about this, and am having trouble figuring it out (as well as short on time due to starting school in a week). Again, any help is immensely appreciated!

 

[bovinda]

Hmm, I can't figure it out. I put in that portion you added, but it still won't default back to what it would be if I clear the F column. I tried adding it at the end also (under 'Check value in F still) but it didn't work either. I can't see why it wouldn't work (not like I'd know anyway, I suppose). It looks like it resembles what you wrote for the original code.

My understanding of the script is that it first checks the value in F, and sees if that has any of the recognized text. If not, it goes on to check column E. After that, at the end, it looks like it checks column F once more to make sure that it has priority, yeah? So I'm not sure why it won't default back if I clear the F column. Clearing the E column does make it default back to black (as long as the recognized text isn't in column F). So it confuses me!

Anyway, I'm not sure I'm explaining it right. It still works very well! If you have any other ideas or suggestions, that's great! If you haven't lost interest and I can explain it better, please let me know, I'd like to understand why it won't work as it seems like it should (to me). Otherwise, and in any event, thank you so much for your help, firefytr!

 

[lenze]

Are you trying to clear more than one cell in column F at a time? If so, this line in the code is the problem

If Target.Cells.Count > 1 Then Exit Sub

You can only clear (or change) 1 cell at a time or the code will not fire.

lenze

 

[Zack Barresse]

Sure, no problem. Let's work this through to completion.

The way I would do it is to setup a breakpoint in your code (select the first Sub line and press F9, this will turn your code line red, the red circle is where you can click with your mouse to toggle a breakpoint). Then select the cell that is not giving you the correct results, press F2, then Enter. This basically changes the cell (like double clicking on it and hitting Enter) so the event will fire. You can then step through your code with the F8 key and see what the code is doing. See what it's doing and let us know.

 

[bovinda]

Hi lenze. I think I'm just trying to clear one cell at a time. Say, for example, that F10 has "Util, Gas" in it (which correctly changed the font color in B10 and D10). I hit delete in F10, clearing "Util, Gas", but the font color stays plum in cells B10 and D10, it doesn't revert to black.

Hi firefytr. I have two questions. First, and this may sound ridiculous, but, uh, which is the first line of the Sub? Is it "Option Explicit" all the way at the top? Or after the line, at "Private Sub Worksheet_Change(ByVal Target As Range)"? Or something else?

Second question...sorry, this may also be basic...I'm not sure which cell is giving incorrect results. Would you say it was B10 and D10, which are not reverting to the correct font colors when I clear F10? Or would you say it is F10, because clearing it doesn't cause the font color to revert in cells B10 and D10?

Thanks for your patience, you guys.

 

[bovinda]

OK, after messing around for a bit, I realized that "Private Sub Worksheet_Change(ByVal Target As Range)" is the earliest it would let me put in the breakpoint, so I figure that must be the beginning of the Sub.

I've tried going through the script with variations of "Debit" or nothing in E10 and "Util, Gas" or nothing in F10 and seeing where it goes to in the code. This is really interesting! I never knew you could do anything like this.

I noticed that when I cleared cell F10, it would go to the 'Case 6', it would go through 'Case "Util, Gas", "Util, Power"' and then it would end, I think. So I tried changing 'Case ""' to 'Case Else' like it was under 'Case 5'. Er...I mean, I changed this:

Case 6    'col F

         'Check value in F
        Select Case Me.Cells(Target.Row, "F").Value
        Case "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
        Case ""
            Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
            Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
        End Select
        Exit Sub

to this:

Case 6    'col F

         'Check value in F
        Select Case Me.Cells(Target.Row, "F").Value
        Case "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
        Case Else
            Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
            Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
        End Select
        Exit Sub

Now it goes through 'Case Else' when I clear cell F10, and the result is that clearing F10 does indeed make the font color in B10 and D10 go to black. I felt very proud! But then I realized that it made the font go to black even if cell E10 still had "Debit" in it. I was hoping that leaving "Debit" in cell E10 would make the font color revert to color 55 instead of black. I'm not sure how to correct that? I'll keep playing around with it. Am I making any sense?


EDIT: is there some way to make it fire for cell E10 all over again after it has fired for F10, if I'm clearing F10?

So for example, I delete the data in F10, causing the code to fire (I think, yeah?). It goes through the code for Case 6, getting to 'Case Else', where it changes the font back to black in cells B10 and D10. Then, normally, it seems like it would finish; at least, it goes to 'End Select', then 'Exit Sub' and stops. At that point, if and only if it went through Case Else (because I was clearing the cell F10), is there some way to make it automatically fire the script for cell E10, so it would see that "Debit" is still in E10, go through the script, and change the font to color 55, or see that there is no recognized text in E10, and leave it black?


EDIT 2: so I started trying to mess around with the script a little bit. I noticed that the way firefytr wrote it, it first checks which column it's firing in reference to. If it's firing in reference to column F, it runs Case 6 and then stops. But if it's firing in reference to column E, it runs Case 5, then goes back and looks at column F again.

So I tried putting in a similar...uh, clause at the end of Case 6--to go back and look at column E. But then I realized that's giving priority to column E (which I didn't want, I do want column F to have priority). So...there must be some way to only make it double check column E at the end of Case 6 if it processes Case Else, yeah? Sorry if I'm repeating myself, I'm hoping I get clearer each time.

 

[Zack Barresse]

Hey, glad to see so much testing going on! You'll learn so much that way!! It's the best way, IMHO.

Okay, so I can see we need to restructure some of the coding logic here. I think we might get rid of the column select case. Let me see if I understand the entire logic here...

Any cell changed in E or F below row 5 should be checked for logic
Col F value should be checked first always
Col E value is checked, but always after col F value
If col F value is "Util, Gas", "Util, Power", (B & D) colorindex = 18
If col F value is blank, (B & D) colorindex = xlautomatic
Go through col E cases, color accordingly.

The only thing we need to know is order of precedence and when/if you want to exit on any certain condition(s).

 

[lenze]

You'll learn so much that way!! It's the best way, IMHO.

So true Zach. Was playing around. See if this logic makes sense

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column < 5 Then Exit Sub
    If Target.Column > 6 Then Exit Sub
    Dim myColor As Variant
    Select Case Target.Column
        Case Is = 6
        Select Case Target
            Case Is = "Util, Gas", "Util, Power": myColor = 18
            Case Else
                Select Case Target.Offset(0, -1)
                Case Is = "Debit", "ATM Withdrawal": 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": myColor = 9
                Case Else: myColor = xlAutomatic
                End Select
        End Select
        Case Is = 5
        Select Case Target.Offset(0, 1)
            Case Is = "Util, Gas", "Util, Power": myColor = 18
            Case Else
                Select Case Target
                Case Is = "Debit", "ATM Withdrawal": 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": myColor = 9
                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
End Sub

I think I covered all the bases, but not sure.

lenze

 

[Zack Barresse]

Logic looks good to me!

 

[bovinda]

lenze, that works perfectly! Exactly what I was looking for. I think I even understand some of what you did, which is fun. But of course I never would have known how to do that.

Thank you so much, lenze and firefytr! I got a lot out of this--besides just the script, obviously. I really appreciate both of your help and patience!

Gratefully, Jeff