+ Reply to Thread
Results 1 to 15 of 15

VBA code to change font color in range of cells when certain text is entered?

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    VBA code to change font color in range of cells when certain text is entered?

    Hello, forgive me in advance as I am very inexperienced with VBA and conditional formatting, especially in Excel 10 (most of the help I've found online is 03 or 07). Here's what I'm trying to accomplish:

    I have a worksheet that is used to calculate GPA based on letter grades and credits. In the column where the letter grade is entered, I want the text to turn red if the grade is a C- or lower (C-, D+, D, D-, F). If the grade is C or higher, I want the text to stay black. If C- or lower is entered in the cell and the text turns red, I want it to turn black if a C or better is later entered in that cell. The range of cells on the worksheet is E10:E30. Also, the sheet is protected, but these specific cells are not. Any suggestions? Thanks ins advance for your help!

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    To the Sheet module in question.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    Thanks for the help. I entered this code but it doesn't seem to be doing anything. Any ideas?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    It only runs when you change any cell(s) in E10:E30
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    Hmm, still not doing anything for me. I opened what you just attached, enabled content, typed "c-" into e10, it was black. Changed it to "d", it was still black.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    You said

    C-, D+, D, D-, F

    Not

    c-, d+, d, d-, f

  7. #7
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    I didn't realize it was case sensitive. One last question, then (hopefully). How do I go about altering this line of code to include both cases of each letter?

    Please Login or Register  to view this content.
    I tried what I thought would be right, but it wasn't.

    Thanks for your help and patience.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    Add one line
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    Ahh, that's so easy, thank you. Sorry, ONE more. How do I make this work with a protected sheet? Otherwise, this works perfectly!

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    Add 2 lines
    Please Login or Register  to view this content.
    If password protected, change to
    Please Login or Register  to view this content.
    as well as Protect line.
    Last edited by jindon; 12-21-2012 at 05:04 PM. Reason: comma deleted

  11. #11
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    I get "Compile error: Wrong number of arguments or invalid property assignment" and the following line is highlighted yellow in the VBA code:

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    OOps,

    No comma...
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-13-2012
    Location
    Denver
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA code to change font color in range of cells when certain text is entered?

    Thank you SO very much! Happy holidays to you.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,954

    Re: VBA code to change font color in range of cells when certain text is entered?

    You are welcome and the same to you.

  15. #15
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA code to change font color in range of cells when certain text is entered?

    hi BrianG, there is a built-in feature to do that - Conditional formatting, why bother with codes
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1