+ Reply to Thread
Results 1 to 5 of 5

How to autocolor a cell based on a dropdownand apply it to multiple sheets.

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    How to autocolor a cell based on a dropdownand apply it to multiple sheets.

    Hello,
    Any help is really appreciated.

    What I want to know is how to apply a conditional formatting on multiple cells(from multiple sheets) based on a drop down from Sheet1.

    Let me attach my file.



    For example when I selected the "grade" which is grade 1 it should automatically highlight the advanced, intermediate and superior. I was able
    to do that in the first sheet. My issue is that I need to do this in 100 Sheets and doing it manually can take forever. Is there any way for a much faster approach?

    Thank you.
    Attached Files Attached Files

  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/365
    Posts
    51,998

    Re: How to autocolor a cell based on a dropdownand apply it to multiple sheets.

    1st, if you have 100 sheets, then you probably have 99 too many. The generally accepted approach is to have ONE (or perhaps two) sheets for your data, then use a few additional sheets for extracts, summaries, reports etc.

    2nd, you should try and avoid using merged cells if at all possible - they cause nothing but problems with formulas.

    3rd, if you use numbers instead of words (1 instead of Grade1), then you could use MATH to determine if a cell should be highlighted. You currently show 1-3 for the CF, so the single rule could be
    =Sheet1!$B$5<4
    You would 1st highlight the range in store1, then apply that CF rule.

    Then, highlight the range in Store1, copy it, click Store 3 sheet tab, scroll to the end of your tabs, hold SHIFT and click the last sheet. You have now GROUPED all the sheets - what you do in 1, will be done in them all
    Now click on B6 and paste.
    It should have copied the CF to all sheets

    Click back onto sheet1 to UNgroup
    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
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    Re: How to autocolor a cell based on a dropdownand apply it to multiple sheets.

    Thanks for the tips.

    May I ask is there any workaround if I am using an alphanumeric for the"grades" (grade1, grade2, etc.) and can it be done similar to the math procedure where you can apply a rule and group it?

    The thing is went I select grade1 in sheet 1, all the box on all other sheets will be filled as well similar to the 2nd sheet.

    Thank you.

  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/365
    Posts
    51,998

    Re: How to autocolor a cell based on a dropdownand apply it to multiple sheets.

    Sure, try this as teh rule, instead...
    =--RIGHT(Sheet1!$B$5,1)<4

  5. #5
    Registered User
    Join Date
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    Re: How to autocolor a cell based on a dropdownand apply it to multiple sheets.

    Thanks but it seems not to be working,

    Please bear with me, I tried pasting the values but it is not working in similar to the one I uploaded.

    It is highlighting all boxes instead when I select grades1to3 and when I tried grouping it will only reflect what I type on sheet1 then on other sheets but not when someone is choosing a drop down result(grade1,grade2, etc.)

    If you don't mind can you please edit my test file and show me a working formula on it. I would really appreciate it.

    Thank you
    Last edited by g0dmenuelz; 02-20-2019 at 04:21 AM. Reason: no dice.

+ 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. Apply Macro to multiple Sheets
    By almst791 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2017, 05:02 PM
  2. [SOLVED] Apply macro to multiple sheets.
    By dzugan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2013, 12:57 AM
  3. Apply VBA code to multiple sheets
    By PaulusKabouter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2013, 11:37 AM
  4. [SOLVED] Add border & apply conditional formatting to sheets based on cell value
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2013, 11:02 AM
  5. How do I apply a filter across multiple sheets
    By Dan1956 in forum Excel General
    Replies: 0
    Last Post: 08-07-2012, 09:33 AM
  6. Is It Possible To Apply Changes To Multiple Sheets
    By globalpontoon in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-16-2011, 07:45 PM

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