Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA coding to colour text in Excel

    Hi,

    I cannot work out how in VBA to colour a row of text a certain colour (in this case blue) if the value of a certain cell contains a value, in this case the word 'Ceiling'.

    See screenshot.

    I would like to add the VBA into an Excel macro, if it is possible that is.

    Any help would be appreciated.

    JJ Capture.JPG

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.J.,

    Welcome to the Lounge as a new poster!

    No Need for VBA.

    1. Select your range of cells say A24
    2. On the Home Tab select Conditional Formatting
    3. Select New Rule
    4. Select Use a formula to determine which cells to format
    5. In the Format values where this formula is true: box type =Upper($D2)="CEILING"
    6. Click the Format... button
    7. Click the Fill tab
    8. Select your desired color
    9. Click OK
    10. Click OK


    CondFmt.PNG

    If you really want VBA you can adapt the code in this post.
    HTH
    Last edited by RetiredGeek; 2016-07-27 at 10:43.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    J.J.,

    Welcome to the Lounge as a new poster!

    No Need for VBA.

    1. Select your range of cells say A24
    2. On the Home Tab select Conditional Formatting
    3. Select New Rule
    4. Select Use a formula to determine which cells to format
    5. In the Format values where this formula is true: box type =Upper($D2)="CEILING"
    6. Click the Format... button
    7. Click the Fill tab
    8. Select your desired color
    9. Click OK
    10. Click OK


    CondFmt.PNG

    If you really want VBA you can adapt the code in this post.
    HTH

    Hi,

    Thanks for responding.

    This is fine but l need to do this each time l open the spreadsheet, so will have to keep doing the Conditional Formatting each time, which can be time consuming.
    Is there some VBA coding to automate the process please.

    JJ

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jini,

    Once you set it it will stay as long as you save the workbook after the initial setup. The range will also auto adjust if you insert rows within the range. However, setting up a Dynamic Range Name (search on that here) will auto adjust the list as long as there are no blank lines.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am unclear what you mean , are you able to attach an example spreadsheet with the VBA to do this in a macro please.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.J.,

    Ok here's a file that contains both methods.

    Sheet 1 demonstrates the Conditional Formatting with a Dynamic Range Name method.

    Sheet 2 demonstrates the Macro method using the WorkSheet_Change Event to trigger the macro.

    Note: the macro also turns the Text color white so you can see it with dark backgrounds. If you use a lighter background color you can comment out the lines that change the font color.

    Sample File: Example Using Conditional Formatting with Dynamic Range.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,


    Thanks for that, l have some more questions.

    1. I could not see macro for sheet2? , can you send me the code in an attached document.

    2. The word 'Ceiling' would not occur in the same cell each time, would the conditional formatting still work?

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.J.,

    The macro for sheet 2 is actually in the Sheet2 Module. Double click it in the VBE.
    Sheet2Module.PNG

    As long as Ceiling is in Column D within the range you specified setting up the conditional formatting it will work.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have tried to set up conditional formatting but it does not save the rules not does it highlight a row just the value 'Ceiling'.

    Also , how do l view the macro for sheet 2 is actually in Sheet2 Module. You mention Double click it in the VBE, how do l do this?
    I could not see an option to do this, see attached.
    Capture.JPG

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.J.,

    From the main Excel window Press Ctrl+F11 to bring up the VBE (Visual Basic Editor) then click on the Sheet2 Module highlighted in yellow in my post #8 above.

    Check out the definition of the conditional formatting in my sample file by clicking Conditional formatting Manage Rules...

    You might want to consider Googling "Excel Tutorials" there is a lot of good free material available.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    When l clicked Ctrl+F11 to bring up the VBE, all it did was to create a new worksheet?

    Can the conditional formatting be put into a macro? instead of being run each time?

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    OOPS! My Bad.

    That's Alt+F11...Sorry!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    OOPS! My Bad.

    That's Alt+F11...Sorry!

    Hi,

    I changed coding to replace the word 'Ceiling' with 'Wall' but the output remained the same and for some reason it did not work.
    See attached coding and output.

    Any help as to what l am doing wrong would be appreciated.ouput.JPG coding.JPG

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.J.,

    You'll notice the UCase() in the Select Case statement. That converts whatever is in the Cell temporarily to all CAPS for comparison purposes so you don't have to worry about what the user types in e.g. Wall, wAlL, wALL, etc.

    Thus in the Case statement you MUST type it in all caps, e.g. Case "WALL"

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    New Lounger
    Join Date
    Jul 2016
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    J.J.,

    You'll notice the UCase() in the Select Case statement. That converts whatever is in the Cell temporarily to all CAPS for comparison purposes so you don't have to worry about what the user types in e.g. Wall, wAlL, wALL, etc.

    Thus in the Case statement you MUST type it in all caps, e.g. Case "WALL"

    HTH

    Hi,

    I done this but still no change to text in spreadsheet (i.e in does not appear blue), l am confused l must be missing something or doing something wrong please can you assist.Capture.JPG

Page 1 of 2 12 LastLast

Posting Permissions

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