+ Reply to Thread
Results 1 to 7 of 7

If value meets criteria, highlight adjacent cells. repeat across sheets

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Florida, United States
    MS-Off Ver
    Excel 2003
    Posts
    11

    If value meets criteria, highlight adjacent cells. repeat across sheets

    I need someway to loop through the first 5 worksheets in my workbook, looking through four specific columns (F, K, P, U) for a value less than or equal to .05. If a value meeting that criterion is found, I need for that cell, as well as the four cells to the left of it (for a total of five cells) to be highlighted (any color is fine). I have attached a copy of my sheet as reference.

    SPSS output.xls

  2. #2
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    Hi matrix_machine,

    Please see attached for a possible solution. I've always found it easier to write the formula first and test it on the cells in question (see text/formula in green).

    Hope this helps.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    Florida, United States
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    Hey,

    I see that you were able to highlight everything correctly, however, I can't seem to determine how you did it. There's no macro's in the workbook, nor is there any sort of apparent VBA script. I see, on the green text, that you checked your work? I was more hoping though for some sort of code to accomplish this, that I could maybe play around with a bit.

    I'm accessing this from home, where I have Excel 2007 as opposed to the 2003 version I use at work, if that makes a difference.

  4. #4
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    Sorry, wouldn't have a clue where to start with VBA.

    It was all done with conditional formatting. I have used this same method with 2003 with great success in the past, all you essentially do is use the formula (green highlight) and copy it across the respective cells. I'll work on another template explaining in more detail if you like and forward it on later. Failing that, I found a similar solution on youtube....see link below:

    http://www.youtube.com/watch?v=LOaezdoyJDE

  5. #5
    Registered User
    Join Date
    06-04-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    It looks like he might have gone through conditional formatting. On the home tab click Conditional Formatting in the "Styles" section, then manage rules. You can see he has the calculations in there.

  6. #6
    Registered User
    Join Date
    07-31-2012
    Location
    Florida, United States
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    Ohhh okay, I should be able to figure this out (and use it for other things, of course!) now that I know it exists in the first place. Thanks!

  7. #7
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: If value meets criteria, highlight adjacent cells. repeat across sheets

    My pleasure!


+ 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