+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting Issues

  1. #1
    Registered User
    Join Date
    10-17-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    2

    Conditional Formatting Issues

    I work for a training organisation and am setting up basic record keeping spread sheets for online students to download and complete with a macro button that unhides an identical sheet. On this duplicate sheet I'm using the Conditional Formatting function to shade in green the cells where they get the answer correct and red shaded cells if it is incorrect.

    In each document I'm building, there can be up to 100 individual cells to do the conditional formatting to. To make it quicker by default the cells are shaded red and my conditional formatting basically says when the answer sheet is equal to their answer, shade the cell green. I had been doing this for 2 frustrating days on each individual cell when it dawned on me that I can use the format painter tool to create the rules on each cell quickly and then just go into each cell and edit the rule with the correct cell reference.

    Now that I've done this they seem to all be linked to the final cell that I pasted to using the format painter tool. If that cell is correct then all cells go green but if that cell is incorrect then they all stay red. I deleted the rule for the final cell and they all linked to the 2nd to last cell doing the same thing. When I go in to look at the rules they all have unique cell references and all the cell references are absolute.

    I have a pretty basic understanding of Excel and what I do know I learned from Google and YouTube. Any help would be appreciated.

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,575

    Re: Conditional Formatting Issues

    Hi, welcome to the forum

    Using the format painter to "copy" CF rules can be iffy at best.

    A few things to consider...
    1. Use the Applies To window to set the range you need the CF to be applied to
    2. Use the absoluting option ($) carefully, if you "lock" the wrong way, it wont look where it's supposed to
    3. if you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-17-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    2

    Re: Conditional Formatting Issues

    Hi Ford, thanks for your prompt response.

    I feel like a bit of a goose, I now realise that when I used the format painter button I highlighted a range of cells instead of each individual cell which is what linked them. When I went into the rules manager I saw in the 'Applies to' field that the range was not correct. That will teach me for trying to take shortcuts, I wish there was an auto fill function to do the CF for me(I'm doing it again).

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,575

    Re: Conditional Formatting Issues

    As long as you figured it out, and got where you needed to be

+ 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