Help with VBA script for changing font color in a cell

B

bovinda

Board Regular
Joined
Jun 11, 2005
Messages
87
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!
 
Sort by date Sort by votes
For what it's worth, if I can ever give you guys 2nd year medical student advice, please let me know! :biggrin:
Click to expand...
Heh, I just may do that. :wink:
Click to expand...

Zach: What's a healthy young fireman need medical advice for. It's more like us old guys that might have discrete inquiries.

lenze
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I plan to start school again for my Paramedic degree here shortly. ;)
 
Upvote 0
Hey you guys, I have one further question. Everything is working perfectly, but I wanted to see if it would be possible to add one other cell to check for color. Does it complicate things too much, or is this possible...

So right now, to check what color column B should be, it refers to the tex in Column E. However, if column F has specific phrases--like "Util, Gas" or "Util, Power"--then the color of the font in columns B and D would be 18 (by firefytr's handy color chart).

In other words, even if cell E10 says "Debit", if cell F10 says "Util, Gas" then the font color of cells B10 and D10 would be 18. If F10 said anything else, then it would still refer to E10 for what the color should be.

Does this make sense? Hopefully this is possible, if not it's not such a big deal, but it would be cool if it was possible. Here is the code as it is so far:


Code: 
Option Explicit

Const MYRANGE As String = "E:E"

Private Sub Worksheet_Change(ByVal Target As Range)

    'Ensure only one cell is being changed
    If Target.Cells.Count > 1 Then Exit Sub
    
    'Ensure cell being changed is in desired range
    If Intersect(Target, Me.Range(MYRANGE)) Is Nothing Then Exit Sub
    
    'Ensure cell being changed is not less than row 10
    If Target.Row < 5 Then Exit Sub
    
    'Check value of cell
    Select Case Target.Value
    Case "Debit", "ATM Withdrawal"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 55
        Me.Cells(Target.Row, "D").Font.ColorIndex = 55
    Case "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 47
        Me.Cells(Target.Row, "D").Font.ColorIndex = 47
    Case "Work, Jeff"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 5
        Me.Cells(Target.Row, "D").Font.ColorIndex = 5
    Case "Work, Pam"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 13
        Me.Cells(Target.Row, "D").Font.ColorIndex = 13
    Case "Dep, Other"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 31
        Me.Cells(Target.Row, "D").Font.ColorIndex = 31
    Case "CC Pay WF", "CC Pay AI", "CC Pay HSBC", "CC Pay AmEx"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 9
        Me.Cells(Target.Row, "D").Font.ColorIndex = 9
    Case Else 'Un-color if none of the above
        Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
        Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
    End Select
    
End Sub

In any event, whether or not you guys can help, I will still always be happy to offer my semi-professional 2nd year med student advice! :)
 
Upvote 0
Add a Select Case to Each Case
Code: 
Case "Debit", "ATM Withdrawal" 
          Select Case Target.Offset(0,1)
          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 = 55 
                Me.Cells(Target.Row, "D").Font.ColorIndex = 55 
           End Select
Case "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam" 
            'repeat as above
'etc

HTH

lenze
 
Upvote 0
I think you could just add a boolean check to it...

untested



<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Const</SPAN> MYRANGE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E:E"

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#007F00">'Dimension variable</SPAN>
    Dim blnNoChange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    
    <SPAN style="color:#007F00">'Ensure only one cell is being changed</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'Ensure cell being changed is in desired range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Me.Range(MYRANGE)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'Ensure cell being changed is not less than row 10</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Row < 5 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'Set boolean variable</SPAN>
    blnNoChange = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#007F00">'Check value of cell</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
    <SPAN style="color:#00007F">Case</SPAN> "Debit", "ATM Withdrawal"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 55
        Me.Cells(Target.Row, "D").Font.ColorIndex = 55
    <SPAN style="color:#00007F">Case</SPAN> "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 47
        Me.Cells(Target.Row, "D").Font.ColorIndex = 47
    <SPAN style="color:#00007F">Case</SPAN> "Work, Jeff"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 5
        Me.Cells(Target.Row, "D").Font.ColorIndex = 5
    <SPAN style="color:#00007F">Case</SPAN> "Work, Pam"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 13
        Me.Cells(Target.Row, "D").Font.ColorIndex = 13
    <SPAN style="color:#00007F">Case</SPAN> "Dep, Other"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 31
        Me.Cells(Target.Row, "D").Font.ColorIndex = 31
    <SPAN style="color:#00007F">Case</SPAN> "CC Pay WF", "CC Pay AI", "CC Pay HSBC", "CC Pay AmEx"
        Me.Cells(Target.Row, "B").Font.ColorIndex = 9
        Me.Cells(Target.Row, "D").Font.ColorIndex = 9
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'Un-color if none of the above</SPAN>
        Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
        Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
        blnNoChange = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
    <SPAN style="color:#007F00">'Check value in F</SPAN>
    <SPAN style="color:#00007F">If</SPAN> blnNoChange = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Me.Cells(Target.Row, "F").Value
        <SPAN style="color:#00007F">Case</SPAN> "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#007F00">'other stuff if you want..</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



Let us know if this works.
 
Upvote 0
Hi lenze, thank you, that works really well! I only have one final question on that. Right now, it will not change the font in B and D to color 18 unless I first enter "Util, Gas" in F10, and then enter "Debit" in E10, in that order. Is it possible to make it so the order doesn't matter--that is, as long as "Debit" is in E10, entering "Util, Gas" in F10 will change the color to 18? And so that deleting "Util, Gas" in F10 will cause the color to revert back to Debit's assigned color without having to reenter "Debit" (since it requires reentry of "Debit" right now)?

This is not essential, of course, it's just sort of a fine-tuning thing, if it's possible. :) I apologize if this seems petty. It's all to make it easy to use for myself and my wife in keeping track of our expenses while we're in school.
 
Upvote 0
Your code is only going to fire when you change a cell in column E. It can be adjusted to handle both columns. I think, however, we may need to start our thinking over from the start. The way we have added conditions has made for sloppy and unefficient code. Let me think about that.

Did you try Zach's suggestion using the booleon switch? That might work better.

lenze
 
Upvote 0
This will check F & E for values, see if it helps ...



<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Const</SPAN> MYRANGE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E:E,F:F"

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#007F00">'Ensure only one cell is being changed</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

    <SPAN style="color:#007F00">'Ensure cell being changed is in desired range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Me.Range(MYRANGE)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

    <SPAN style="color:#007F00">'Ensure cell being changed is not less than row 10</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Row < 5 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

    <SPAN style="color:#007F00">'Check column...</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
    <SPAN style="color:#00007F">Case</SPAN> 6    <SPAN style="color:#007F00">'col F</SPAN>

        <SPAN style="color:#007F00">'Check value in F</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Me.Cells(Target.Row, "F").Value
        <SPAN style="color:#00007F">Case</SPAN> "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

    <SPAN style="color:#00007F">Case</SPAN> 5    <SPAN style="color:#007F00">'col E</SPAN>

        <SPAN style="color:#007F00">'Check value of E col cell</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
        <SPAN style="color:#00007F">Case</SPAN> "Debit", "ATM Withdrawal"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 55
            Me.Cells(Target.Row, "D").Font.ColorIndex = 55
        <SPAN style="color:#00007F">Case</SPAN> "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 47
            Me.Cells(Target.Row, "D").Font.ColorIndex = 47
        <SPAN style="color:#00007F">Case</SPAN> "Work, Jeff"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 5
            Me.Cells(Target.Row, "D").Font.ColorIndex = 5
        <SPAN style="color:#00007F">Case</SPAN> "Work, Pam"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 13
            Me.Cells(Target.Row, "D").Font.ColorIndex = 13
        <SPAN style="color:#00007F">Case</SPAN> "Dep, Other"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 31
            Me.Cells(Target.Row, "D").Font.ColorIndex = 31
        <SPAN style="color:#00007F">Case</SPAN> "CC Pay WF", "CC Pay AI", "CC Pay HSBC", "CC Pay AmEx"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 9
            Me.Cells(Target.Row, "D").Font.ColorIndex = 9
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>    <SPAN style="color:#007F00">'Un-color if none of the above</SPAN>
            Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
            Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

        <SPAN style="color:#007F00">'Check value in F still</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Me.Cells(Target.Row, "F").Value
        <SPAN style="color:#00007F">Case</SPAN> "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



HTH
 
Upvote 0
Hey firefytr, sorry I missed your post! Well, I tried the last script you posted, and it worked very well! I really like what it's doing now. There is only one thing that I'm still wondering about...would it be possible that if I were to clear the text in column F, the font color in B and D would then automatically revert to whatever it should be otherwise?

For example, right now, if I enter "Util, Gas" in F10, regardless of what is in E10 the font color in B10 and D10 changes to 18, which is perfect! However, if I clear F10 or enter something else, the font color does not change back (say, to black if there's nothing recognized in E10, or to 55 if it says "Debit"). I must reenter "Debit" in E10 to get it back to font color 55 (or clear E10 to get it back to black).

Otherwise it works perfectly and is already much, much better than it was before! Is it possible to make these last changes? If not, it is okay, of course! I really appreciate your help.
 
Upvote 0
Well, right now it exits the sub routine if the value in col F matches that in the Select Case (first) case statement. If you want to check for a blank value in F, just add another case statement...


<font face=Tahoma New>        <SPAN style="color:#007F00">'Check value in F</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Me.Cells(Target.Row, "F").Value
        <SPAN style="color:#00007F">Case</SPAN> "Util, Gas", "Util, Power"
            Me.Cells(Target.Row, "B").Font.ColorIndex = 18
            Me.Cells(Target.Row, "D").Font.ColorIndex = 18
        <SPAN style="color:#00007F">Case</SPAN> ""
            Me.Cells(Target.Row, "B").Font.ColorIndex = xlAutomatic
            Me.Cells(Target.Row, "D").Font.ColorIndex = xlAutomatic
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Is that what you are looking for? If not, post any other requirements.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Change font color if cell value changes
Replies
3
Views
364
kevin9999
K
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
325
offthelip
O
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
400
Nathan Asius
N
H
  • Solved
VBA to change color of Forms Control Button
Replies
6
Views
649
skillilea
S
D
  • Solved
When cell with formula changes -> edit adjacent cell
2 3
Replies
21
Views
1K
Dani_LobP
D

Forum statistics

Threads
1,216,109
Messages
6,128,883
Members
449,477
Latest member
panjongshing

Share this page

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