+ Reply to Thread
Results 1 to 21 of 21

Changing row colours based on cell values

  1. #1
    Registered User
    Join Date
    08-10-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    13

    Changing row colours based on cell values

    I have created a code which colours from A2 to M2 based on the value in column M2. For example, if M2 = "Yes", the cells colour green, if M2 = "No", the cells colours red and for any other situation the cells remain blank. The code also runs automatically at the moment which I want it to do.

    However, now I've got this working I want to apply it right from row 2 to row 1500. I'm very new to VBA and so this is probably very basic, but I'd appreciate any help!

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    Hello stormhayward,

    Any reason why you do not apply Conditional Formatting to solve your issue?

    If you need help with same, attach a sample workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Changing row colours based on cell values

    Please Login or Register  to view this content.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    Try this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    @ walruseggman & xladept,

    Don't you guys know my temper yet?LOL

    What is wrong with applying Conditional Formatting instead of using VBA?

    Regards.
    Last edited by Winon; 08-11-2017 at 01:01 PM. Reason: Spelling

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    Don't get smart Tjaart! But seriously, I hadn't seen your post - old friend

    I think that formulas and conditional formatting just "mess up" an otherwise pristine sheet.

    OK - so I'm a bigot - it's an identity of a sort

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    LOL, you guys rock!

    Kind regards.

  8. #8
    Registered User
    Join Date
    08-10-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    13

    Re: Changing row colours based on cell values

    Hi, thanks for the help, but neither of those codes work, nothing happens when I use them. Any idea why? Also, I'd rather use coding as it's a lot neater than conditional formatting

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,395

    Re: Changing row colours based on cell values

    You haven't provided a sample workbook yet.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Registered User
    Join Date
    08-10-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    13

    Re: Changing row colours based on cell values

    Here's a sample workbook. I've only filled in to row 20, but I want the formula to fill in down to row 1500. It's pretty self-explanatory what I'm trying to do, but maybe this workbook will help explain.
    Attached Files Attached Files

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Changing row colours based on cell values

    Hi, thanks for the help, but neither of those codes work
    Yep they both do....
    Attached Files Attached Files
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  12. #12
    Registered User
    Join Date
    08-10-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    13

    Re: Changing row colours based on cell values

    Sorry, I was typing in Yes and No without capitals, my mistake! Also, is there any way for these codes to work if I don't manually type in 'Yes' or 'No'? I.e. the cell will change to yes or no via a formula rather than being manually typed.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    Here this doesn't care about the case:

    Please Login or Register  to view this content.

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    Hello stormhayward,

    Replace all your Code for the BEFORE Sheet to:

    Please Login or Register  to view this content.
    @ xladept, please don't kill me!

    Regards.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    @ Winon -
    Please Login or Register  to view this content.
    just a small adjustment to your code

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    @ xladept,

    The Option Compare Text takes care of the:

    Please Login or Register  to view this content.
    Please try it.

    Actually the Correct Code should be:

    Please Login or Register  to view this content.
    Play around with it by quoting out the Option Compare Text, and see what happens.;

    Best regards.
    Last edited by Winon; 08-14-2017 at 02:24 PM. Reason: Corrected Code changes

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    @ Winon - great code - and thanks for the lesson on Option Compare Text - I may use it in the future

  18. #18
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    @ xladept,

    You are welcome my Friend, thank you for the much appreciated valuable interaction!

    Best Regards.

  19. #19
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Changing row colours based on cell values

    Hello stormhayward,

    I notice that you visited the Forum a couple of times, after some additional solutions were offered in this Thread, without any comment?

    For your convenience I am now attaching a sample Workbook with the Code suggested in the later posts.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-10-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    13

    Re: Changing row colours based on cell values

    Amazing, thank you all so much for your help, that's exactly what I was looking for!

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Changing row colours based on cell values

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Line chart with changing colours depending on values
    By Invicta084 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-16-2015, 10:33 AM
  2. Changing Cell Colours Based on Conditions
    By NatalieEC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2014, 11:26 PM
  3. help in changing identical values to certain colours
    By sandbach in forum Excel General
    Replies: 1
    Last Post: 06-17-2011, 09:30 AM
  4. changing cell color based on changing values
    By tvonbehren in forum Excel General
    Replies: 2
    Last Post: 09-16-2009, 12:33 PM
  5. Changing bar graph colours based on score peercentages
    By peakymatt in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 06-03-2008, 07:43 AM
  6. Changing Cell colours
    By johncon in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-05-2008, 07:47 AM

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