+ Reply to Thread
Results 1 to 16 of 16

difficulty writing code for changing font color if cell contains specific text

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    difficulty writing code for changing font color if cell contains specific text

    I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?

    I REALLY APPRECIATE IT!
    Last edited by curbster; 08-12-2009 at 06:58 PM. Reason: making it more specific

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: How do I code this in VBA? If cell contains "S" mark entire row RED.

    curbster,

    Please attach your workbook (or a sample) - scroll down and see "Manage Attachments".
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: How do I code this in VBA? If cell contains "S" mark entire row RED.

    here is a sample with the values I need to manipulate in their respective columns.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: How do I code this in VBA? If cell contains "S" mark entire row RED.

    curbster,

    See the attached workbook "letterS(1) - SDG09.xls" with macro "FindSinAD".

    Open the file and run the "FindSinAD" macro.
    Attached Files Attached Files

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: How do I code this in VBA? If cell contains "S" mark entire row RED.

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: difficulty writing code for changing font color if cell contains specific text

    That code works perfectly! Thank you Stanly! There is a little more I have to do and have tried and cannot figure it out. I also have to look for the letter "C" within the same column and mark the rows red and change the column "L" value to a negative number, which I did figure out by duplicating a bit of the code.

    The part I am struggling with however is adding to this code that if column "AD" contains an "O" then I need to delete the entire row. The code I wrote does not catch all of the "O"'s and I have to run it twice.

    I will include a sample as to show what I'm dealing with. Here is the code I am using:

    Please Login or Register  to view this content.
    Thanks for any help!
    Kelly
    Attached Files Attached Files
    Last edited by curbster; 08-12-2009 at 01:57 PM. Reason: forgot to add code

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    I strongly recommend that you actually spend a few minutes reading the Forum Rules that you agreed to when you joined. If you don't & continue ignoring them then you will find yourself unable to post!

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    Thanks for taking the time to read and comply with the rule .

    Have you considered using Conditional Formatting?

    http://excel-it.com/excel_conditional_formatting.htm

  10. #10
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: difficulty writing code for changing font color if cell contains specific text

    I'm not familiar with conditional formatting outside of the ":Format\conditional formatting" option which is very limited.

    I'm open to anything that works!

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    Conditional Formatting will change the colours, but I see you want to change values as well. Unfortunately CF can't do that.

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    This should work and will be faster - only looping through the column once
    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: difficulty writing code for changing font color if cell contains specific text

    curbster,

    Try:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: difficulty writing code for changing font color if cell contains specific text

    this works BEAUTIFULLY!

    Thank you!!

    P.S.~ I also added about a thousand rows and it works for all rows and I could not find a discrepancy.

  15. #15
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: difficulty writing code for changing font color if cell contains specific text

    Roy, Thank you for your help. Yours worked fine unless there were no "O" in column AD, in that case, it would stop the macro and give me a highlighted line. Out of all the files I had to go through only about 5 of them didn't have an "O" in AD so it worked most of the time, thank you!

    Stanley, Your code worked for all of my files, thank you, you solved the problem!

    Thank you thank you thank you!
    Last edited by curbster; 08-12-2009 at 06:58 PM.

  16. #16
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: difficulty writing code for changing font color if cell contains specific text

    Just needs a little error handling in case there is no cells containing O.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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