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!

 

[Zack Barresse]

Hi there bovinda!

You could use this worksheet_change event. To install, right click the sheet tab and select View Code, paste on right...



<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">'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 < 10 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</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").Interior.ColorIndex = 21
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 21
    <SPAN style="color:#00007F">Case</SPAN> "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam", "Dep, Other"
        Me.Cells(Target.Row, "B").Interior.ColorIndex = 11
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 11
    <SPAN style="color:#00007F">Case</SPAN> "Work, Jeff"
        Me.Cells(Target.Row, "B").Interior.ColorIndex = 7
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 7
    <SPAN style="color:#00007F">Case</SPAN> 4 <SPAN style="color:#007F00">'set value</SPAN>
        Me.Cells(Target.Row, "B").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> 5 <SPAN style="color:#007F00">'set value</SPAN>
        Me.Cells(Target.Row, "B").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
    <SPAN style="color:#00007F">Case</SPAN> 6 <SPAN style="color:#007F00">'set value</SPAN>
        Me.Cells(Target.Row, "B").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
        Me.Cells(Target.Row, "D").Interior.ColorIndex = 1 <SPAN style="color:#007F00">'set color</SPAN>
    <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").Interior.ColorIndex = xlNone
        Me.Cells(Target.Row, "D").Interior.ColorIndex = xlNone
    <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>



To check out all colorindex values, check out this VBAX KB entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=206

HTH

 

[bovinda]

Hey firefytr, thank you for the quick response. I can't believe you put that together so quickly!

So I right-clicked on the sheet I wanted to apply it to in my workbook, selected "View Code," and pasted the code exactly as you had written it into the box on the right. I then clicked the save icon at the top left and closed the window. However, when I went back and selected "Debit", for example, the color didn't change. I think I must have done something wrong. Is there something further I should do, or more I needed to add to what you wrote that I didn't realize? (I see you left extra space for the other cases...I'll fill those in now just in case that's the problem.)

Thank you so much for your help!

 

[Norie]

bovinda

How did you 'select' Debit?

Where did you 'select' it?

What version of Excel are you using?

If you are usign Data>Validation... then it's only Excel 2000 and later that will trigger the worksheet change event.

 

[bovinda]

Oops...like I said, I am a moron. It does work, I was just trying a cell less than 10. I'll just change that!

The only question I have still is that it is changing the cell color rather than the font color. I think I didn't really specify, but I was hoping it would change the font color and leave the cell color white. Would it be possible to change it so it affects the font instead of the cell?

Thank you so much for your help!

EDIT: thanks Norie, sorry I just missed your post. I really meant that I 'typed' "Debit" in rather than selecting it. That wasn't very clear of me. (I chose the word 'select' just because I was choosing it in a drop down menu in the E column, but I think it's the same as typing it?) And just in case it is relevant, I'm using Excel 2003.

 

[lenze]

For Font, change

Me.Cells(Target.Row, "B").Interior.ColorIndex = 21
to
Me.Cells(Target.Row, "B").Font.ColorIndex = 21

lenze

 

[bovinda]

Thanks lenze, that did the trick! It works almost perfectly now. The only thing is when I change the text in E10 from "Debit" to, say, "Cash" or some other unrecognized text to make the font go back to black, instead of reverting to black text it says that there is an error in the line italicized below:

Case Else 'Un-color if none of the above
Me.Cells(Target.Row, "B").Font.ColorIndex = xlNone
Me.Cells(Target.Row, "D").Font.ColorIndex = xlNone

Is that because it must be told a font color (black) rather than having no font color?

 

[lenze]

Hi: Instead of xlNone, use
xlAutomatic

lenze

 

[bovinda]

Hey lenze, that did the trick! Thank you so much firefytr, lenze, and Norie for responding and helping me out. I really appreciate it! For what it's worth, if I can ever give you guys 2nd year medical student advice, please let me know!

 

[Zack Barresse]

For what it's worth, if I can ever give you guys 2nd year medical student advice, please let me know!

Heh, I just may do that. :wink: