+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Everyone,

    I'd like to make a group of cells change color based on the result of one cell in the row. So I'd like to make cells A1-AA1 have a red background if the word NO is found in column G1, or turn yellow if the word YES is found there. I'd need to do this individually for about 150 rows. Some will be Yes and some will be NO (and some will be blank until they are reviewed for the Yes/No decision, so no formmatting in some cases)

    Can you do this with conditional formatting, or do I need a bit of VBA code? If VBA code, do I have to save the file as XLSM to apply it? I've only found a few ways to do this with code, but I was hoping someone could suggest an easier way.

    Thanks!

    Vaslo

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    The code is beautiful, so if there is no way to with conditional formatting, so be it, I'll use this.

    Thanks for taking the time to post this. I'll mark as solved if no ideas come from clever conditional formatting experts. I suspect it will still be tricky given the number of rows I need to update.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    You can use the conditional formatting interface...

    Add two rules...

    Conditional Formatting
    • Highlight applicable range >> A1:AA150
    • Home Tab >> Styles >> Conditional Formatting
    • New Rule >> Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =$G1="No"
    • Edit the Rule Description: Format values where this formula is true: =$G1="Yes"
    • Format… [Number, Font, Border, Fill]
    • OK >> OK
    HTH
    Regards, Jeff

  5. #5
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Jeff,

    This is perfect. Thanks to both you and RCM for your help on this.

    For my own education, I don't quite get how it knows to look row by row. I assumed that when I looked at this it would ALWAYS look to G1 for the value, so I was pleasantly surprised when it DID look line by line. I supposed it looks in a reference manner instead of absolutely as indicated by you $G1 instead of $G$1?

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    $G1 >> (absolute column and relative row)

    Yes the $ sign in front of the G locks the column, but no dollar sign in front of the row allows the row to change as the evaluation moves down the sheet.

    Switch between relative, absolute, and mixed references

  7. #7
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Jeff,

    I probably made my question sound to elementary. I do understand relative and absolute references (locking rows and not columns as well, etc.)and setting them with excel. What I didn't understand is how they worked with conditional formatting. What I guess I don't get it how it knows to look row by row like that in the formatting. For example, what if I wanted it to look at 2 rows for a particular value instead of what and then had a large selection - if it was relative would it then look at every TWO rows?

    Maybe that's not the right question, I'm just trying to understand how these formulas world with relative references in conditional formatting.

    Thanks again.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Well I'll be honest, hard to explain some things as I've never given them too much thought.

    I'll do my best to explain in "Jeff" language (which BTW, my wife never understands )

    The conditional formatting is relative to the first cell you highlight as your range.

    So in your case, you want to highlight rows in the range of A1:AA150. Before you enter the conditional formatting you MUST highlight the range starting with A1. This is the anchor point.

    Whenever I am entering conditional formatting, I always look at the name box (right above column A) to see what the anchor point is.

    If instead, you start the highlight from AA150 and back up towards A1, the conditional formatting would now reference from the anchor point and down (AA150).

    The results would not come out as you expect.

    With your two row example, hard to say how to implement as it would depend on the layout of the data.

    Hope this helps and maybe somewhere on-line you can find some other descriptions to fill in any remaining holes, but if not, please don't hesitate to ask any follow-up questions you may have.

    http://www.myonlinetraininghub.com/h...nal-formatting

  9. #9
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Conditional formatting or VBA to color a group of cells based on Yes/No in one cell

    Still very helpful and a good start for understanding. Pulling the values backwards is another interest wrinkle I didn't know about. I'll got through the source you mentioned. Thank you again.

+ 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