+ Reply to Thread
Results 1 to 15 of 15

Conditional Formatting based on another sheet's cell

  1. #1
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9

    Conditional Formatting based on another sheet's cell

    Hi Everyone,

    please guide me to my weird requirement. (Office 2010)
    Let say i have a workbook containing 2 sheets, Sheet 1 & Sheet 2.
    i need a group of cells on Sheet 2 to change colors, if value of Sheet1!A1 changes.
    Basically am trying to color those cells to select color name on Sheet 1.

    If Sheet1!A1 = Blue, then Cells on Sheet 2 to turn-up Blue.
    If Sheet1!A1 = Red, then Cells on Sheet 2 to turn-up Red.
    and so on

    please help.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting based on another sheet's cell

    Hi, welcome to the forum

    You would use Conditional Formatting for this (but only if A1 contains the WORD blue, not just the color blue)
    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
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: Conditional Formatting based on another sheet's cell

    Change colour according to A1
    Attached Files Attached Files
    Kamboj
    _________________________________________________________________________________
    Mark the thread as SOLVED if my answer satisfy you.

  4. #4
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9
    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    You would use Conditional Formatting for this (but only if A1 contains the WORD blue, not just the color blue)
    Yes i will write word blue in cell A1.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting based on another sheet's cell

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =A1="blue" format fill as needed

  6. #6
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9

    Re: Conditional Formatting based on another sheet's cell

    yes, exactly this is required.
    could you please tell me the way to do this?
    i.e. how'd i apply the same to my worksheet?

    thanks a lot

  7. #7
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9

    Re: Conditional Formatting based on another sheet's cell

    Quote Originally Posted by Kamboj View Post
    Change colour according to A1
    yes, exactly this is required.
    could you please tell me the way to do this?
    i.e. how'd i apply the same to my worksheet?

    thanks a lot

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting based on another sheet's cell

    It is unnecessary to use VBA for something like this, regular Conditional Formatting is simpler and easier to modify

  9. #9
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9
    Quote Originally Posted by FDibbins View Post
    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =A1="blue" format fill as needed
    This doesn't work if i have to select multiple values on A1, i.e. Red, Blue, Green etc.
    Kamboj's response does the job perfwvtly.
    I just need an elaboration on that VBA work, as am completely new to this vba/macro concept.
    Thanks

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting based on another sheet's cell

    ok good, as long as it's working for you

    Although if the cell contains all 3, what color do you want it tio change to>

    You could also modify my suggestion to...
    =A1="*blue*"

  11. #11
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: Conditional Formatting based on another sheet's cell

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9

    Re: Conditional Formatting based on another sheet's cell

    Quote Originally Posted by FDibbins View Post
    ok good, as long as it's working for you

    Although if the cell contains all 3, what color do you want it tio change to>

    You could also modify my suggestion to...
    =A1="*blue*"
    At a time there has to be only one specific value in A1, i.e. either Blue or Red or Green or Orange.
    And accordingly the cell selection on Sheet2 should change their color respectively.

  13. #13
    Registered User
    Join Date
    06-18-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    9

    Re: Conditional Formatting based on another sheet's cell

    Quote Originally Posted by Kamboj View Post
    Please Login or Register  to view this content.
    i wrote this, but its working for Blue only.
    please do let me know the mistake i did





    Private Sub Worksheet_Change(ByVal Target As Range)
    Sheet2.Activate
    If Sheet1.Range("B7") = "Red" Then
    Sheet2.Range("a1:c12").Select
    Selection.Interior.Color = vbRed
    End If
    If Sheet1.Range("b7") = "Blue" Then
    Sheet2.Range("a1:c12").Select
    Selection.Interior.Color = vbBlue
    End If
    If Sheet1.Range("b7") = "Green" Then
    Sheet2.Range("a1:c12").Select
    Selection.Interior.Color = vbGreen
    End If
    If Sheet1.Range("b7") = "Orange" Then
    Sheet2.Range("a1:c12").Select
    Selection.Interior.Color = vbOrange
    End If
    End Sub

  14. #14
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: Conditional Formatting based on another sheet's cell

    This is Ok. you have to write word as they are here as :- Red, Blue, Green etc. in proper way if you write RED then color will not change.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Conditional Formatting based on another sheet's cell

    Data can be entered as "Red" or "RED" or "reD" (mixture of upper/lower case)


    Please Login or Register  to view this content.

+ 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. Replies: 1
    Last Post: 07-06-2015, 10:52 AM
  2. [SOLVED] Conditional formatting based on range of cells in different sheet
    By iveta96 in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 06-01-2015, 10:45 AM
  3. Replies: 5
    Last Post: 01-09-2014, 02:13 PM
  4. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  5. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  6. Conditional formatting based on value in diff sheet
    By shikamikamoomoo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2010, 12:02 AM
  7. Conditional formatting based on another sheet
    By goofy78270 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2007, 04:19 PM

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