Changing text color based on information in the cell

  • I am a college student who has created a gradebook template so that I can keep track of my assignments as I get them and keep on top of my grades. That being said, I'm trying to figure out a way that I can code excel to work so that if my current average in the class ever drops below 70%, I want the color of the text to change to red to alert me that the grade is too low and needs to come up. I'm new to using VBA in excel and not sure how to do it or what to code. Any help would be appreciated including telling me what I should do in order to enter the code and check that it works. I've coded before in access but never excel.

  • Re: Changing text color based on information in the cell


    A sample workbook would help - without knowing the layout of your workbook it is difficult to help you too much however Conditional Formatting is a simple solution that would probably suit your needs. Upload a sample workbook with some dummy data and you will get some better advice. This is probably something you can do without VBA.

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Changing text color based on information in the cell


    I've attached a sample gradebook where when I enter the assignments, it shows what the current grade is with only the assignments I've completed so I can keep track of the progress I make as the course goes on. That formatting is ok. What I want to do is code the gradebook or find some way that I can get the percent listed in the "Avg" to turn red and become bold when the percentage drops below 70% as you can see that it has done in the sample grades I've entered for you.
    [ATTACH=CONFIG]57702[/ATTACH]

  • Re: Changing text color based on information in the cell


    If I understand your need correctly then Conditional Formatting will work for you. Follow these steps to set it up.


    1. Highlight the range of cells to receive the Formatting.
    2. Click Conditional Formatting from the Excel Ribbon "Home Tab"
    3. Select the "Highlight Cells Rule" option then select "Between" from the sub menu.
    4. Change the Between settings to 1% and 70% then select "Custom" from the drop down menu.
    5. Under the Font tab select BOLD
    6. Under the Fill Tab select RED as your Fill Color.


    Click OK and your all set.


    I hope this helps.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Changing text color based on information in the cell


    VBA line:


    Code
    if activecell < 0.7 then Activecell.Font.Color = 200 else Activecell.Font.Color = 0


    Miceal_ please take the time to read the Forum Rules, specifically use Code Tags when posting code to the Forum

  • Re: Changing text color based on information in the cell


    Thanks Miceal, your suggestion worked. I varied your suggestion slightly to use the "Less Than" option and say <70% but it still worked all the same. I just have one question for you about the VBA line you provided me with as I want to try out both available options. When you say if activecell how do I specify in the code which cell I'm referring to that needs to change. Also, does the code you provided only change the font color and not the boldness? I'm new to using VBA in excel and not sure how to do it

  • Re: Changing text color based on information in the cell


    You really don't need VBA to do this, you've already been pointed to Conditional Formatting.


    The code snippet only works on the ActiveCell .

  • Re: Changing text color based on information in the cell


    [INDENT=2]I just figured I would give both a try so I have experience with both but knowing that bit of information about the ActiveCell piece, I guess I won't. Thanks royUK. Do either you or Miceal think you could take a look at my other question I posted a few months ago? I haven't seem to have gotten very far with the people who were originally replying to me. The forum can be found at http://www.ozgrid.com/forum/showthread.php?t=181335 [/INDENT]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!